Excluding clustered key columns from non-clustered indexes definitions

Question

As I've read the bookmark or the reference of the non-clustered index to the table clustered index is the clustered index key itself. On the SQL Saturday event I've attended, one of the lecturers said it's always good practice to exclude the clustered key columns from the non-clustered index key definition and include clause, too.

I am wondering are there any negatives of doing so, for the following scenario:

1. Table A has the following columns: RecordID, QuestionID, Pts, PtsOf and many other.

2. I have created a non-clustered index like this:

   CREATE NONCLUSTERED INDEX [IX_TableA_RecordID_QuestionID] ON [dbo].[TableA]
   (
       [RecordID] ASC
      ,[QuestionID] ASC
   )INCLUDE ([Pts],[PtsOf]);
   

   in order to skip reading the clustered index when score is calculated per records and questions. The [RecordID][QuestionID] pair is the clustered index key.

If I change the index like this:

CREATE NONCLUSTERED INDEX [IX_TableA_RecordID_QuestionID] ON [dbo].[TableA]
(
    [Pts] ASC
   ,[PtsOf] ASC
);

The indexes is again used as before (it satisfies the search criteria) even for queries that are not referring the points columns:

SELECT [RecordID]
      ,[QuestionID]
FROM [dbo].[TableA];

SELECT [RecordID]
FROM [dbo].[TableA];

SELECT [QuestionID]
FROM [dbo].[TableA];

So, the engine is smart enough to see using the non-clustered index will reduce the reads and I know leaving it as the original definition is working, too, but some of the indexes are quite big and I think removing some of the keys can reduce their size.

What I am concerned about is that the first index rows were ordered by the bookmark and now they are not. I am wondering if and how this could harm the performance?

Answer 1

On the SQL Saturday event I've attended, one of the lecturers said it's a always good practice to exclude the clustered key columns from the non-clustered index key definition and include clause, too.

I disagree. Let me explain with the table in your question:

The clustered index is: (RecordID, QuestionID) and there are many more columns.

Any non-clustered index will also have the clustered key columns, too, appended in the end. So, an index like:

(Pts)   is equivalent to:   (Pts, RecordID, QuestionID)

and similarly:

(Pts, PtsOf)           <->:  (Pts, PtsOf, RecordID, QuestionID)

(Pts, RecordId)        <->:  (Pts, RecordID, QuestionID)

(Pts, QuestionID)      <->:  (Pts, QuestionID, RecordID)

(Pts) INCLUDE (PtsOf)  <->:  (Pts, RecordID, QuestionID) INCLUDE (PtsOf) 

(QuestionID, RecordID) <->:  (QuestionID, RecordID)

For joint tables or like this one that have a composite primary/unique key - whether it is clustered or not, it's very often to have queries that need the (a,b) index and others that will use better the (b,a) index, sometimes queries that need both. So, one often needs both of these.

If the composite clustered key has more than two columns - say (a,b,c) - it's often that you may need an index on (b,c,a) or in (d,b) and another on (e,c,a) (which of course will be equivalent to (b,c,a), (d,b,a,c) and (e,c,a,b) respectively.) You can't just remove these columns from the definitions of the non-clustered keys because the column order will change.

The suggestion has one good point though. The clustered key columns can be removed from the INCLUDE part. They are redundant, just noise there.

---

About the indexes in the question, a non-CI on (Pts, PtsOf) is equivalent to (Pts, PtsOf, RecordID, QuestionID), so it is very different than the original non-CI on (RecordID, QuestionID) INCLUDE (Pts, PtsOf). It will use a bit more space than the original and of course these two indexes will be useful for different types of queries.

• The (Pts, PtsOf) will be be useful, for example, for queries with WHERE Pts BETWEEN 0 AND 100, WHERE Pts = 200 AND PtsOf = 300, etc.

• The (RecordID, QuestionID) INCLUDE (Pts, PtsOf) is basically a copy of the table with only the 2 clustered key columns and 2 only extra columns (of the many). This is (rarely) useful and it's a form of vertical partitioning. If you often have queries that need all the rows of the table but only these 2 columns, then it's probably one these (rather rare) cases where the extra space and effort to maintain this index is justified.

Answer 2

What I am concern for is that the first index rows were ordered by the bookmark and now they are not?

The rows just happen to be coming out in this sequence. There are many scenarios where the optimiser may choose to return them in a different sequence, even if the table remains the same. The only way to be sure the client receives results in a particular sequence is by writing an ORDER BY clause.

I am wondering if and how this could harm the performance?

Adding an ORDER BY clause may cause the optimiser to choose a different access path involving different indexes which may have a higher overall execution cost. It may requie an explicit sort during query execution, again increasing cost. Depending on the query, tables, data and indexes it may have absolutely no affect whatsoever.

Answer 3

The CLUSTERED INDEX on a table will always contain all of the columns in that table even if you only create the index on one, or in your case, 2 columns. For big tables, wide and/or large record count, that can be a lot of data to pull back when all you want is 2 or 3 columns on one record.

By default all non-clustered indexes will contain the clustered index keys whether explicitly dictated or not. However, performance can differ and a lot of factors need to be taken into account. Non-clustered index leaf-level pages contain the key value and a pointer to the data row in the Clustered Index; a pointer and not the actual data.

Because of this, in the right circumstance, you can see a performance increase in having a CLUSTERED INDEX and a NONCLUSTERED INDEX on the same key. There are many reasons for this, but keeping it simple its because the NONCLUSTERED INDEX will have only the ID data, not the other column data. If you have to, or have had to deal with database tables like I have in the past that are 100 columns wide with tens of millions of records, that "duplicate ID" NONCLUSTERED INDEX could be your best friend when it comes to performance. However if your table is only 20 columns wide and/or a few thousand records, then you most likely will not see improved performance and could potentially see worse. Also, whether you want to create one specific index for each and every query or just 1 or 2 "covering" indexes is another factor you have to consider.