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 |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-02-27 : 11:59:19
|
| Chris writes "HiI have been asked to re-write a SQL query to avoid using IS NULL on an indexed column. SELECT a, b, cFROM Table_AWHERE a IS NULLUsing IS NULL apparently invalidates the INDEX on this column.Is there a way of checking for a NULL value but still taking advantage of the Index?I can't think of a way without changing the underlying table, which I can't do. In this instance the column holds a date and the query has to check if this has been populated.ThanksChris" |
|
|
JamesT
Yak Posting Veteran
97 Posts |
Posted - 2002-02-27 : 12:04:18
|
| Would using IsDate work? Would it invalidate the index too? Is not, using IsDate(column) would return 0 if it is null. |
 |
|
|
Arnold Fribble
Yak-finder General
1961 Posts |
Posted - 2002-02-27 : 12:28:44
|
| I'm not sure what's meant by "invalidates the INDEX". Is the suggestion here that rows with a NULL in the indexed column won't appear in the index? If so, that's certainly not true of SQL Server 2000. |
|
|
|
JamesT
Yak Posting Veteran
97 Posts |
Posted - 2002-02-27 : 12:35:40
|
| I was curious after thinking about this a little more. I recreated the situation having three columns with one being a datetime and having an index. I inserted rows into the table and left two of the datetime columns null. When running it with the show execution plan it used the index just fine when using IS NULL. Are you sure it is not using the index? |
|
|
|
|
|
|
Using IS NULL