hi everybody Pllease guide me about what is the difference with respect to performance in this query 1.) select * from employee where increment >= 0 and increment <= 1000 2.) select * from employee where increment between 0 and 1000 we had a clustered index on increment column whose data type is IDENTITY(1,1), and this table had 1000000 of rows shiv
Check the execution plan. Both are likely to produce an identical plan as BETWEEN is internally resolved to a >= ...<= combination anyway. -- Frank Kalis Microsoft SQL Server MVP http://www.insidesql.de Heute schon gebloggt?http://www.insidesql.de/blogs
both will use Clustered Index seek. so there won't b any differences. if you have choiuce between In and Between between is the best ---------------------------------------- http://spaces.msn.com/members/dineshasanka
quote: if you have choiuce between In and Between between is the best Why? -- Frank Kalis Microsoft SQL Server MVP http://www.insidesql.de Heute schon gebloggt?http://www.insidesql.de/blogs
I think he meant to say if you have choices between In and Between between is the best. Isnt it Dinesh? Madhivanan Failing to plan is Planning to fail
Yes, but unless I am mistaken, it's a matter of personal preferences which one you use. -- Frank Kalis Microsoft SQL Server MVP http://www.insidesql.de Heute schon gebloggt?http://www.insidesql.de/blogs
Can't see how IN applies here. IN can take either a subquery, or a list of specific values, but it does not support ranges of values (unlike the LIKE clause, which supports expressions like [a-z], [0-9] etc.).
I meant the >= ...<= combination is equivalent to BETWEEN. Therefore I wanted to know why Dinesh would prefer BETWEEN. I didn't read the IN here. Probably it was IN BETWEEN. [<img src='/community/emoticons/emotion-2.gif' alt='' />]<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a><br />
