I have a table defined in the following way:
CREATE TABLE [dbo].[MyTable] ( [MyTable_ID] [int] IDENTITY(1,1) NOT NULL, [COLUMN_WITH_DATA] [varchar](128) NOT NULL, [COLUMN_A] [varchar](128) NULL, [COLUMN_B] [varchar](128) NULL, [COLUMN_C] [bit] NOT NULL )
And an index created like that:
CREATE INDEX [MyTable_Index_ABC] ON [dbo].[MyTable] ( [COLUMN_A], [COLUMN_B], [COLUMN_C] ) ON [PRIMARY]
And I run the following query:
SELECT TOP 1 [MyTable].* FROM [MyTable](UPDLOCK) WHERE [MyTable].[COLUMN_A] IS NULL AND [MyTable].[COLUMN_B] IS NULL AND [MyTable].[COLUMN_C] = 0
The goal behind this is to get COLUMN_WITH_DATA vaule of first not yet used record, and then update its' COLUMN_A and COLUMN_B with not null values, to mark it as consumed.
I have a couple of millions of records in MyTable, and about half of them has both: COULMN_A and COLIMN_B that are not NULL (indicating the data was already consumed).
In this situation the query runs really slow, and the execution plan shows that the index:
is not used unless I use a query hint, in which case the query runs much faster. On the other hand if I update all not yet consumed rows, that is having:
COLUMN_A IS NULL AND COLUMN_B IS NULL
so that COLUMN_A and COLUMN_B contain empty string: '' instead of NULL, the index is used and the query runs much faster again.
Two questions would be:
- why null values make my index discarded
- is it possible to instruct the database to always use the index without having to use query hints on possibly multiple different queries ??