0

We are using a SQL monitoring system (Confio) to monitor our SQL Server 2008 R2 Express database. Today it came up with a suggestion for 2 added indexes that appear to be almost identical:

CREATE NONCLUSTERED INDEX [IndexName] ON [dbo].[TestedDevices] ([DeviceNumber],
  [TestRecordID])

and

CREATE NONCLUSTERED INDEX [IndexName] ON [dbo].[TestedDevices] ([DeviceNumber])
  INCLUDE [TestRecordID])

These were not presented as one or the other, they were presented as "you need to add both of these." My question is, what is the difference between the two syntaxes? They both use the exact same fields.

Thanks for any insight! Dave Newman

DaveN59
  • 3,638
  • 8
  • 39
  • 51
  • 1
    See this question: http://stackoverflow.com/questions/1307990/why-use-the-include-clause-when-creating-an-index – TrevorBrooks May 13 '14 at 21:29
  • 2
    Although marked as a duplicate, I'm not 100% sure that answers your question. The specific answer is: if you have the first index, the second is totally unnecessary. The second could be more efficient under some circumstances. – Gordon Linoff May 13 '14 at 21:33

0 Answers0