I am trying to determine why the query optimizer in SQL Server is recommending creating a new index instead of using an existing index that appears to be sufficient for the query.

First the table. Column names changed to protect the innocent :-)

CREATE TABLE [myTable] (
[id] [int] IDENTITY(1,1) NOT NULL,
[serialNumber] [varchar](12) NOT NULL,
[sName] [varchar](64) NOT NULL,
[meanValue] [int] NOT NULL,
[range] [int] NOT NULL,
[modifiedDate] [datetime] NOT NULL,
CONSTRAINT [PK_myTable] PRIMARY KEY CLUSTERED ( [id] ASC )
)


Create the index in question:

CREATE NONCLUSTERED INDEX [IDX_myIndex]
ON [myTable] ([serialNumber], [sName], [meanValue], [range])
INCLUDE ([modifiedDate])


Add data for testing using your generator of choice ;-) Run the following query (table only has a few million records)

SELECT TOP 1000
[serialNumber],
[sName],
[meanValue],
[range],
[modifiedDate]
FROM [myTable]
WHERE [serialNumber] = 137802
AND [sName] = 'A Name'


The query optimizer recommends using a new index where the additional where clauses are covered in the INCLUDE instead of part of the key:

CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[myTable] ([sName])
INCLUDE ([serialNumber],[meanValue],[range],[modifiedDate])


I was under the impression that a broader index that encompasses more columns will be used as the index as long as the order of the WHERE clauses represents the order of the columns indexed.

If I also WHERE on the modifiedData the index gets used and the query optimizer doesn't complain:

SELECT TOP 1000
[serialNumber],
[sName],
[meanValue],
[range],
[modifiedDate]
FROM [myTable]
WHERE [serialNumber] = 137802
AND [sName] = 'A Name'
AND ([modifiedDate] >= '2000-04-25' AND [modifiedDate] < '2019-04-30')


SQL Server 2008R2 - Why is my index not used suggests a closer correlation between the index key and includes with the SELECT statement helps determine index use (but in my example they are basically the same). I have lots of rows, which probably satisfies the row use probability test, and there are no NULLs - thus negating the NULL effect of the index.

I thought, maybe incorrectly, that an index A, B, C, D would cover an query where A, B, C, or A, B, or A would be run. Is this assumption wrong? I realize there could be edge conditions that throw this basic concept off-kilter, but at a fundamental level, isn't this roughly how it's supposed to work?

Thanks in advance for any help, pointing out the stupidity in my ways, recognizing I need to go (back) to DB school etc... :-)

• There is a pretty big difference, the leading key column in one index is SerialNumber and the other is sName. Also for cases where two indexes could equally satisfy a query, and where the indexes are essentially the same size, it's essentially a coin flip for SQL Server. The fact that it recommended a different index probably means that it could have used a different plan (this is just a comment because I haven't looked into the actual details or tried to repro your scenario). – Aaron Bertrand Oct 28 '14 at 20:42
• You assumption about whether the index will be covered or not is a correct assumption. But as Mr. Bertrand is saying, the Cardinality of SerialNumber vs sName could make one index far more useful for a given query than another, even if both indexes cover the query. – mskinner Oct 28 '14 at 21:14
• Can you also clarify? Leaving aside the recommendation, is the IDX_myIndex not used? What is the actual query plan? – ypercubeᵀᴹ Oct 29 '14 at 0:09
• You also have WHERE [serialNumber] = 137802 while the column is a varchar. I'd bet that's the reason for the complaints. – ypercubeᵀᴹ Oct 29 '14 at 0:13
• Why is serialNumber a varchar in the first place if the values it contains are numeric? If it always stores numeric values and leading zeros arent significant then storing as a numeric type will be more compact. – Martin Smith Oct 29 '14 at 8:47

WHERE [serialNumber] = 137802

According to SQL Server's datatype precedence, when two values of different datatypes are compared, the value with the datatype of lower precedence is converted to the datatype of the higher precedence. Unfortunately, int is higher in the list than varchar. This blows up any hope of using the index as the column (serialNumber) values are converted to integers. The column being the 1st position of the index, leads the optimizer to not use that index and search for an alternative (and thus the suggestion.)
Solution is not to have any implicit or explicit conversions of columns in WHERE condition. Simply use:
WHERE [serialNumber] = '137802'