0

I have a non-clustered index on a datetime column (AdmitDate) and a varchar column (Status) in SQL Server. Now the issue is that I'm filtering the result only on the basis of the datetime column (no Index on AdmitDate column alone).

In order for me to utilize the non-clustered index I used a not null condition for the varchar column (Status) but in that scenario the execution plan shows "Index Scan".

select ClientName, ID 
from PatientVisit  
where 
    (PatientVisit.AdmitDate between '2010-01-01 00:00:00.000' AND '2014-01-31 00:00:00.000' ) 
    AND PatientVisit.Status is not null

-- Index Scan

But if I pass a specific Status value then as expected the excution plan shows Index Seek.

select ClientName, ID 
from PatientVisit  
where 
    (PatientVisit.AdmitDate between '2010-01-01 00:00:00.000' AND '2014-01-31 00:00:00.000') 
    AND PatientVisit.Status = 'ADM'

--Index Seek

Should I use in operator and pass all the possible values for the Status column to utilize the non-clustered index?

Or is there any other way to utilize the index?

Thanks,

Shubham

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Shubham
  • 39
  • 1
  • 6
  • 1
    You state: *I have a Non Clustered Index on datetime column (AdmitDate)* and: *No Index on AdmitDate column*. Which of the two is true? – Giorgos Betsos Feb 16 '15 at 13:02
  • 1
    Can you show us exactly how you create that index? What is the order of the columns in the index? – marc_s Feb 16 '15 at 13:04
  • Also: I bet the `Index Seek` is on your actual nonclustered index, while the `Index Scan` is on the clustered index (e.g. the table itself) - right? – marc_s Feb 16 '15 at 13:11
  • Not posting as an answer as a bit of a guess but try reversing the index. And as Marc said the scan might be the optimal. – paparazzo Feb 16 '15 at 14:05
  • Hi Giorgos, the table has a 'composite' Non Clustered Index on AdmitDate and Status(AdmitDate,Status). and no index on the AdmitDate column... – Shubham Feb 18 '15 at 08:44

2 Answers2

1

You're using SELECT ClientID, Name and you fetch columns that are not part of the index, SQL Server will need to go to the actual data page to get those column values.

So if SQL Server finds a match in the non-clustered index, it will have to do an (expensive) key-lookup into the clustered index to fetch the data page, which contains all columns.

If too many rows have a Status that is NULL, SQL Server will come to the conclusion that it's faster just the bloody scan the whole index, rather then doing a great many index seeks and key lookups. In the other case, when you define a specific value, and that matches only a few (or only one) rows, then it might be faster to actually do the index seek and one expensive key lookup.

You thing you could try is to use an index which includes those two columns that you need for your SELECT:

CREATE NONCLUSTERED INDEX IX_PatientVisit_DateStatusIncluded
ON dbo.PatientVisit(AdmitDate, Status)
INCLUDE(ClientID, Name)

Now in this case, SQL Server could find the values it needs to satisfy this query in the index leaf page, so it will be a lot more likely to actually use that index - even if it finds a lot of hits - possibly with an Index Scan on that small index (which isn't bad, either!)

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
0

Create a filtered index. You can then create an index for the datetime field only for values where status is not null.

CREATE NONCLUSTERED INDEX FI_IX_AdmitDate_StatusNotNull
  ON dbo.PatientVisit(AdmitDate)
  WHERE Status IS NOT NULL

This will be used for your query where Status IS NOT NULL and your existing index will be used for queries where Status = 'ASpecificValue'

Andy Nichols
  • 2,952
  • 2
  • 20
  • 36