Why does the below query perform an Index Scan rather than an Index Seek?

I would have thought the Index would act as a covering index?

ON [dbo].[UserData] 
    [CustomerID] ASC,
    [FileDate] ASC
INCLUDE ([UserID],[User])

-- query
SELECT c.User, c.UserID 
FROM ClientData c
WHERE CustomerID = @CustomerID
AND FileDate >= @StartDate
AND FileDate <= @EndDate

Execution Plan posted to dropbox


  • 3
    Your first two sentences are very different questions. Are you wondering why this is a Scan vs. Seek? Or are you wondering why your index (IX_CustomerFileDate) wasn't chosen over another index? Commented Jan 6, 2014 at 15:25
  • 1
    It is a covering index, but that doesn't mean that it should do a seek instead of a scan
    – Lamak
    Commented Jan 6, 2014 at 15:28
  • 1
    Without seeing the actual execution plan, we could only guess. Guesses don't make for good answers. Could you post the actual (not estimated) plan somewhere, please? Commented Jan 6, 2014 at 15:28
  • Hi, execution plan added.
    – K09
    Commented Jan 6, 2014 at 15:46
  • 3
    There is a huge difference between the estimated rows and the actual rows. Have you updated the statistics of that table recently?, doing that should make the optimizer choose a better plan for your query
    – Lamak
    Commented Jan 6, 2014 at 16:08

1 Answer 1


The Execution plan shows a non clustered index scan:

enter image description here

Because you have a implicit conversion of your first index seek column SQL Server decided to do a full non clustered index scan. It's not doing a clustered index scan because the non clustered index is a covering index. 1)

enter image description here

You can see that the column customerID needs to be converted to a NVARCHAR(50) datatype.(red conversion). Because the column first needs to be converted, it can't be used. Since it's the first column in your index, it effectively makes the index useless for seeks or estimates.

Why is SQL Server doing a implicit conversion of the column? Because on the right side of the = sign you feed it a Nvarchar. Your not doing this explicit, since also the parameter @CustomerID first gets implictly converted to a NVARCHAR(3) (blue conversion).

I presume this is because your are filling @CustomerID with a Unicode value. However, It would suprise me that you would use a unicode values and yet have non unicode datatypes defined in your table. Maybe somebody else could confirm the reason for the blue conversion.

1) I deleted a wrong assumtion about the relevance of the amount of rows returned that Martin Smith was nice enough to point out to me.

  • 1
    Regarding the blue conversion I think this is because the collation of the variable is different from the collation of the column. If I create a database with default collation COLLATE SQL_Ukrainian_CP1251_CI_AS and query a column of datatype SQL_Latin1_General_CP1_CI_AS I see that for example. Commented Jan 7, 2014 at 8:05
  • Hi @Edward, I updated @CustomerID to match the table data type - varchar(50). It now uses an Index Seek, but is still doing conversions... Seek Keys[1]: Prefix: [TestData].[dbo].[UserData].CustomerID = Scalar Operator(CONVERT_IMPLICIT(varchar(50),[@CustomerID],0)), Start: [TestData].[dbo].[UserData].FileDate >= Scalar Operator([@StartDate]), End: [TestData].[dbo].[UserData].FileDate <= Scalar Operator([@EndDate])........ Also Estimated Number of Rows is still huge compared to Actual Number of Rows.
    – K09
    Commented Jan 7, 2014 at 10:40
  • @k09 - Conversions of the parameters aren't such a problem. Presumably that is the collation issue. And you will almost certainly get the same plan anyway even if estimated vs actual is accurate. Did you update the statistics on the table though? Also in the XML version of the plan what are ParameterRuntimeValue and ParameterCompileValue? Commented Jan 7, 2014 at 10:44
  • @k09 - Actually looking at your previous plan the values aren't sniffed at all. Are you using variables rather than parameters? You can add OPTION (RECOMPILE) to get accurate cardinality estimates but the plan will probably still end up the same. Commented Jan 7, 2014 at 10:54
  • @Martin. Statistics updated on this specific Index. Is this enough? By Parameter do you mean using question mark? Using variables e.g. declare @StartDate datetime = '2014/01/03'
    – K09
    Commented Jan 7, 2014 at 12:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.