1

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!

0

1 Answer 1

1

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.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.