I've never really understood the difference between these two indexes, can someone please explain what the difference is (performance-wise, how the index structure will look like in db, storage-wise etc)?
Included index
CREATE NONCLUSTERED INDEX IX_Address_PostalCode
ON Person.Address (PostalCode)
INCLUDE (AddressLine1, AddressLine2, City, StateProvinceID);
'Normal' index
CREATE NONCLUSTERED INDEX IX_Address_PostalCode
ON Person.Address (PostalCode, AddressLine1, AddressLine2, City, StateProvinceID);
The internal storage of indexes uses a B-Tree structure and consists of "index pages" (the root and all intermediate pages) and "index data pages" (the leaf pages only).
INCLUDE
section, less data per index key is stored on each page.When an index is used, the index key is used to navigate through the index pages to the correct index data page.
INCLUDE
columns, that data is immediately available should the query need it.INCLUDE
columns, then an additional "bookmark lookup" is required to the correct row in the clustered index (or heap if no clustered index defined).Some things to note that hopefully addresses some of your confusion:
INCLUDE
columns).INCLUDE
columns as well.)It's worth noting that before
INCLUDE
columns were added as a feature:INCLUDE
columns basically allow the same benefit more efficiently.