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 |
|
jonathans
Starting Member
40 Posts |
 Posted - 2009-07-08 : 23:49:00
|
| Hi guysI've noticed that I don't get optimal index usage when writing a where clause in the following manner.WHERE [Id] = ISNULL(@Id, [Id])But when I write the where clause like this, it uses the appropriate index.WHERE (@Id IS NULL OR [Id] = @Id)Any idea's as to why? The first option is obviously neater and better in my view, though the index usage says otherwise, and I am just curious as to the reason.ThanksWARNING: Running on cold coffee! |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2009-07-09 : 00:03:41
|
| 1st is causing a scan. 2nd allows a seek.2nd is the *right* way if you're going to allow optional paramatersReason is that the optimizer can't know the value of the param to use an index seek (since you're passing it to a function that may modify it) |
 |
|
|
|
|
|
Odd index usage