0

I'm relatively new to the world of SQL Server Optimization and Indexes. I ran a query that recommends missing indexes (https://blog.sqlauthority.com/2011/01/03/sql-server-2008-missing-index-script-download/) and I'm having trouble understanding the differences of the Include clause.

The only difference in my two indexes is that Index1 contains the 'Email' column and Index2 does NOT. Would both of these indexes be required or will Index1 be sufficient? I believe only Index1 is needed but I'm not sure.

CREATE INDEX [Index1] 
ON [ActiveDirectory].[dbo].[ActiveDirectory] ([MailEnabled], [Active]) 
INCLUDE ([EmployeeID], [DisplayName], [Email])


CREATE INDEX [Index2] 
ON [ActiveDirectory].[dbo].[ActiveDirectory] ([MailEnabled], [Active]) 
INCLUDE ([EmployeeID], [DisplayName])

Thank you!

Griz

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Carl Grzywacz MSFT
  • 23
  • 6

2 Answers2

0

Only the first index is required.

If you got both suggestions, it's because one query required all three include columns (a 'covering index') to satisfy the query without having to do a lookup on the primary clustered index to grab the columns... and a second query only required two of them.

One covering index with all three include columns satisfies both queries.

Trying to create both would create a lot of duplicate data and slow down inserts more and use more disk space for no good reason.

pmbAustin
  • 3,890
  • 1
  • 22
  • 33
0

It depends on what columns do you need in your select statement If you need all three [EmployeeID], [DisplayName], [Email] - create Index1

Otherwise, create Index2

Roughly - the fields that you use in your WHERE predicate should be in the ON part of the index, and the columns that you use in SELECT should be in the 'INCLUDE' (if they are not already mentioned in ON)

See Creating Indexes with Included Columns for an example

Both indexes at the same time, are most definitely - not needed.

Basically, the columns in the INCLUDE part of the index do not participate in the index structure, but rather are attached to the index so you don't have to go back to your table and look up the data via PK.

You are able to efficiently "look up" by the columns in the ON clause: [MailEnabled], [Active] , and you can include them in select without extra cost.

You are also able to select the columns in your INCLUDE clause at no extra cost [EmployeeID], [DisplayName], [Email] - but searching (filtering, joining, lookup) on them will not be fast

ironstone13
  • 3,325
  • 18
  • 24