Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
 Posted - 2006-07-06 : 04:44:44
|
| I read somewhere that if there is table with index on some column like in the example below:create table #test(i int primary key clustered, somecol varchar(100))and if I write the query like below:Select * from #test where Abs(i)>=1In the query execution plan, I get Index Scan instead of Seek since there is function on index column i. But if I write the query given below:Select * from #test where IsNull(i,0) >= 1what I get in the execution plan is Index Seek. So my question is why the above rule does not apply for the second query? Is it somehow related to the determinism of the function? Or something else?Thanx in advance for any help !!Harsh AthalyeIndia."Nothing is Impossible" |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2006-07-06 : 06:35:28
|
| I suspect that's because i is not nullable and the server realises that IsNull(i,0) >= 1 is the same as i = 1If you made it nullable I think you would get a scan.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2006-07-06 : 06:41:23
|
quote:
Originally posted by nr
I suspect that's because i is not nullable and the server realises that IsNull(i,0) >= 1 is the same as i = 1If you made it nullable I think you would get a scan.
Cheers nr!! I think you are right...I tried that and it indeed resulted in Index scan....Thank you very much for the help.Harsh AthalyeIndia."Nothing is Impossible" |
|
|
|
|
|
|
Strange Results