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 index is the way the data is physically structure. In a phone book, that's usually by last name. But if you wanted to look something up by a different field, say PhoneNumber, you probably wouldn't want to re-print an entire phone book ordered by PhoneNumber. Instead, you could create an index in the back of the book which lists phone numbers, and a reference to the page you could find those phone numbers in the main part of the book.

So if you wanted to look up the first and last name of the person with the phone number 555-555-5555, it might say "the last name of the person with this phone number is 'Smith'. You would then flip to the page that has the "S"s and start looking for Smith. (note, this is called a "clustered index lookup" when performed in SQL), and then read the persons first and last name.

At a minimum, that index has to have the index key (PhoneNumber) and where you can find that phone number in the phone book. But now say I don't want to have to flip back through the phone book to see the person's first name. I could annotate the index to also contain the first name of the person associated with that phone number so that I never have to flip back through the book looking through all the "S" names. That's what INCLUDE column's are. They're not information you can search on, they're just bits of data from the clustered index which are copied to the index structure so you don't need to do that lookup on the primary key.

So now we get back to the matter at hand. What happens when you include a column that's part of the primary key? Short answer, nothing. Those column's are already part of the index's physical structure because of the pointer from the index to the clustered index. SQL is smart enough to realize this, and doesn't copy those values twice or have to do extra maintenance on them. So the size and overhead of the index is the same.

So should you include them or not? You may also have noticed that if you look at an execution plan, SQL will often suggest indexes which include clustered index. While I'm not personally a fan of including those columns, there isn't any performance hit for doing so. And it was pointed out to me that if you DO explicitly include columns from the primary key to cover a query, should that primary key change, those columns will STILL be covered. There's something to be said for that, but if you're changing a primary key on a production table, that may be the least of your worries.

So what did we learn? INCLUDE-ing primary key columns in a non-clustered index is totally unnecessary as those references already exist. However it also does no harm to the performance of queries or the overhead of maintaining that index. It may just come down to whether you'd like to have your index INCLUDE list nice and tidy, or if you think there's value to explicitly stating that those columns need to be covered. Personally, I prefer tidy, but I can see it the other way too.

Hopefully you now know a little more about what the end result of doing this is.