SQL Server 2008R2 - Why is my index not usedBut I cannot delete the records that were consumed while dequeing. This is due to the fact, that a consumer that comes for the data again should get the same data that was returned the first time. COLUMN_A and COLUMN_B are there to identify the consumer...

Jun28

comment

SQL Server 2008R2 - Why is my index not usedThanks. I have thought of that and seen the stackoverflow thread earlier. But the questions still stays unresolved with respect to why nulls discard the index and empty strings does not...

Jun28

comment

SQL Server 2008R2 - Why is my index not usedIn addition I do not quite understand how using tables as queues would help me in this case... Could you explain a bit more? What complicates the situation a little bit is that COLUMN_A and COLUMN_B are used as consumers identificators. This is to ensure that a consumer with the same id's will not get a fresh record but an already consumed one. This part of logic is done via a separate query. So only if the search by consumers identifications returns nothing, a first free row is returned. Search by consumers ids uses the index and is very fast.

Jun28

comment

SQL Server 2008R2 - Why is my index not used@Martin - with every query change I also update the data, so that the query makes sense - meaning that it will actually return the first not yet consumed row, whether the not consumed stated is marked by NULLs or empty strings. Same goes for the COUNT(*) queries.

Jun28

comment

SQL Server 2008R2 - Why is my index not usedWhen I do a SELECT COUNT(*) FROM ... WHERE COLUMN_A IS NULL AND COLUMN_B IS NULL the index is actually used and the estimated rows count is about the numer of NULL rows. When I update NULLs to empty strings an run WHERE COLUMN_A = '' AND COLUMN_B = '' the index is used and the estimated number or rows is 1

Jun28

comment

SQL Server 2008R2 - Why is my index not usedChanging the select * to the list of columns in index still doesn't show the index being used in execution plan. When the desired index is not used the PK index is used insdead... What puzzles me the most is the effect that updating nulls to empty strings has on the query...