Sql-server – Non-clustered index with WHERE clause

indexnonclustered-indexsql server

I have a large table (around 100 million rows). I am trying to optimize searches. Most of my searches use two columns: bar nvarchar(64) and foo nvarchar(64).

I am only interested in foo when:

foo = 'string01'
OR
foo = 'string02'
OR
foo IS NULL

I was thinking on creating the following non clustered index:

CREATE INDEX ixbar 
ON myTable (bar) 
INCLUDE (foo) 
WHERE foo IN ('string01', 'string02') OR foo IS NULL

But the OR statement is not allowed in a WHERE clause for an index. This makes me think that probably I am not approaching this correctly.

I would appreciate any suggestion on the most efficient way of doing this.

Thank you!

Best Answer

Handling NULLs is tricky, sure. You could get around needing two indexes with either a computed column, or by updating the NULLs in your current column to some canary value.

ALTER TABLE dbo.MyTable ADD foofighter AS ISNULL(foo, 'canarystring');

CREATE INDEX fooey ON dbo.MyTable (bar) INCLUDE(foofighter) 
WHERE foofighter IN ('string01', 'string02', 'canarystring');

Adding non-persisted computed columns has fewer locking implications up front, and you can still index them any which way makes you happy.

Related Question