23 reputation
13
bio website
location Poland
age
visits member for 2 years, 5 months
seen Jun 28 '12 at 16:02

Jun
3
awarded  Notable Question
Aug
15
awarded  Popular Question
Jun
28
awarded  Scholar
Jun
28
accepted SQL Server 2008R2 - Why is my index not used
Jun
28
comment SQL Server 2008R2 - Why is my index not used
Thanks for the links. I will be sure to check them out.
Jun
28
comment SQL Server 2008R2 - Why is my index not used
But 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...
Jun
28
comment SQL Server 2008R2 - Why is my index not used
Thanks. 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...
Jun
28
comment SQL Server 2008R2 - Why is my index not used
In 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.
Jun
28
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.
Jun
28
comment SQL Server 2008R2 - Why is my index not used
When 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
Jun
28
comment SQL Server 2008R2 - Why is my index not used
Changing 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...
Jun
28
awarded  Student
Jun
28
asked SQL Server 2008R2 - Why is my index not used