Posts

Showing posts from June, 2017

Primary Key columns in INCLUDE list

I was reviewing someone's code the other day and I saw they had put primary key columns in the INCLUDE list of a non-clustered index

-- Create a table if object_id('tempdb.dbo.#PhoneBook') is not null drop table #PhoneBook create table #PhoneBook ( FirstName nvarchar(500), LastName nvarchar(500), PhoneNumber varchar(20), DOB datetime ) create clustered index #IXC__#PhoneBook__LastName on #PhoneBook (LastName) create nonclustered index #IXN__#PhoneBook__PhoneNumber on #PhoneBook (PhoneNumber)
A little background on the physical structure of non-clustered indexes for those who might not know. A non-clustered index consists of at least two, and up to three components. All non-clustered indexes store the index key (in my example above, this would be [PhoneNumber] in the nonclustered index) as well as a pointer back to the clustered index (in this case, the columns [LastName]).

The Phone Book example is the classic indexing example thrown around. The clustered …