May 5, 2005 at 12:12 pm
Hi,
Are there any restrictions about the order of the conditions in a where clause related to the way in wich the index of the table that is being searched was created?
for example:
table1(field1, field2, field3, etc..)
index idx1(field1, field2, field3)
select field4 from table1
where field1 = @f1
and field2 = @f2
and field3 = @f3
is it the same that the next query?
select field4 from table1
where field2 = @f2
and field3 = @f3
and field1 = @f1
thanks in advance
*** Ygnacio Durán ***
May 5, 2005 at 12:13 pm
Yes it's the same query... and they'll all most likely end up as clustered index scan (or bookmark lookup).... unless field4 is the primary key of the table.
May 6, 2005 at 1:11 am
I would expect a Clustered Index Seek (in case that index is a clustered one). Respectively an Index Seek along with a bookmark lookup in case the index is a nonclustered one. Though I'm assuming here a highly selective index.
Ygnacio, see if this helps:
http://www.microsoft.com/sql/techinfo/tips/development/queryopstats.asp
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/statquery.asp
http://support.microsoft.com/default.aspx?scid=kb;EN-US;195565
http://support.microsoft.com/default.aspx?scid=kb;EN-US;325024
http://support.microsoft.com/default.aspx?scid=kb;EN-US;820209
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply