1

Given following example:

CREATE NONCLUSTERED INDEX IX_Address_PostalCode  
ON Person.Address (PostalCode)  
INCLUDE (AddressLine1);  
GO

If I included more columns in the INCLUDE list like:

CREATE NONCLUSTERED INDEX IX_Address_PostalCode  
ON Person.Address (PostalCode)  
INCLUDE (AddressLine1, AddressLine2, City, StateProvinceID, Latitude, Longitute, Zip, Country, etc... );  
GO

Does this require more space on disk?

Or put differently: is data that is covered in the INCLUDE list duplicated on the leaf nodes of the index?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Enceradeira
  • 387
  • 4
  • 15
  • 1
    Everything in a nonclustered index is duplicated data, either in the key or in the included columns. Such an index will have a very big overhead. – Alejandro Oct 17 '18 at 12:22
  • 1
    Yes. You can probably find a deeper answer to your question here: https://stackoverflow.com/questions/41791413/indexes-with-included-columns-whats-the-difference – JMabee Oct 17 '18 at 12:27
  • 1
    At the extreme end of the scale, including every column in the index would effectively mean you're storing the table twice, with the rows in different order. – Jeroen Mostert Oct 17 '18 at 12:27

1 Answers1

3

Yes.

And that is precisely the reason of the include: Sacrifice some space but make (some) queries not having to do a key lookup when they need the included columns, but take them from the index itself.

George Menoutis
  • 6,894
  • 3
  • 19
  • 43
  • 3
    Worth pointing out that you're not merely sacrificing space, but also spending more I/O on modifications. Whether the tradeoffs are worth it depends, of course. – Jeroen Mostert Oct 17 '18 at 12:29