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.
Best Answer
When you created the clustered index with
ename
the rows in the table were shuffled to be in that order. When you dropped the index the rows were not reshuffled.There is no guarantee that the rows will be returned by a query in the order that they are stored, but often this is what happens.
If the order is important then you would add an
ORDER BY
(and would be well advised to cluster the table on that field too to avoid sorting every time).But don't choose a clustering key just because of that. For a more thorough discussion of clustering and heaps see http://kejser.org/clustered-indexes-vs-heaps/