NonClustered Index Scan Problem I had an issue with a Clustered Index Scan. So I got all columns in the Output list of the Index Scan in SSMS and I created a NonClustered Index for these columns.

Now however, I do not see any performance difference even though it is using the new NonClustered Index. Instead it is now doing a NonClustered Index Scan.

How do I get it to do a seek? 


This is the Predicate shown in the plan

CASE WHEN (1) THEN 'SGD' ELSE [UserTbl].[Name] END=[UserTbl].[Name]
How do I get it to do a seek?


Given the predicate on the table you have shared with us:

CASE WHEN (1) THEN 'SGD' ELSE [UserTbl].[Name] END=[UserTbl].[Name]


It seems likely you are using an OPTION (RECOMPILE) hint on the query in a version of SQL Server that does not support the Parameter Embedding Optimization (PEO) fully. This requires at least SQL Server 2008 SP1 CU5.

Without that, the recompile hint is not an effective way for dynamic search queries to seek on an index. This type of query is characterized by expressions like:

column = @variable OR @variable IS NULL
column = COALESCE(@variable, column)


If you are not using one of those constructions, it would be something broadly similar. If full parameter embedding were available, the predicate you have would be simplified to:

'SGD' = [UserTbl].[Name]


...which could use a nonclustered index with [Name] as the leading key to perform a seek.

You need to do one or more of the following:


Upgrade to a more recent version of SQL Server (adding an OPTION (RECOMPILE) hint if not already present)
Rewrite the query, perhaps using dynamic SQL, so that the expression is seekable using an index with Name as the leading key
Ensure Name is the leading column in the index


Further reading:

Parameter Sniffing, Embedding, and the Recompile Options
Dynamic Search Conditions in T-SQL