Clustered Index - Query Performing Index Scan

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?

CREATE NONCLUSTERED INDEX [IX_CustomerFileDate] 
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

Thanks!

The Execution plan shows a non clustered index scan:

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)

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.

StackExchange Database Administrators Q#56214, answer score: 7