3

I ran a very basic query against one of our table and I noticed that the execution plan query processor is recommending that we create an index on a column

The query is 

SELECT SUM(DATALENGTH(Data))
FROM Item
WHERE Namespace = 'http://some_url/some_namespace/'

After running, I get the following message

// The Query Processor estimates that implementing the following index could improve the query cost by 96.7211%.
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[Item] ([Namespace])

My problem is that I already have such index on that column:

CREATE NONCLUSTERED INDEX [IX_ItemNamespace] ON [dbo].[Item] 
(
    [Namespace] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

Why is Sql Server recommending me to create such index when it already exist?

T.J. Crowder
  • 1,031,962
  • 187
  • 1,923
  • 1,875
Pierre-Alain Vigeant
  • 22,635
  • 8
  • 65
  • 101

1 Answers1

0

Index column and sort order make a difference...

Previous SO 1 and SO 2

However, for something this simple try adding an INCLUDE clause to make it covering.

CREATE NONCLUSTERED INDEX [IX_ItemNamespace] ON [dbo].[Item] ([Namespace]) INCLUDE ([Data])
Community
  • 1
  • 1
gbn
  • 422,506
  • 82
  • 585
  • 676