I'm not sure if I'm doing something wrong here but I have a query running on a table with millions of rows.
The query is something like this:
from dbo.table with (index (index_1), nolock)
where col1 = 15464
and col2 not in ('X', 'U')
and col3 is null
and col4 = 'E'
CREATE NONCLUSTERED INDEX [index_1] ON [dbo].[table] ([col1], [col2], [col3], [col4]) WITH (FILLFACTOR=90) ON [PRIMARY]
For this query:
select * from table where col1 = 15464 and col2 not in ('X', 'U') and col3 is null and col4 = 'E';
The best index is
table(col1, col4, col3, col2). The query should use the index automatically, without a hint.
When choosing an index based on a
where clause, you should put the equality conditions in first -- followed by one column with an inequality. For the purposes of indexing,
not in are inequality conditions in general.
Also, if you mix data types, then sometimes indexes are not used. So, this assumes that
col1 is numeric.