1) IF PlayerId is assigned with NEWSEQUENTIALID, you could consider that as the clustered index.
2) Otherwise, you can add an IDENTITY and make that clustered (questionable benefit, since all access will be through the PK you have already established).
3) Or you can leave it as a heap - with appropriate non-clustered indexes.
My order of preference would be 1, 3, 2 assuming you can't change the uniqueidentifier to an IDENTITY instead.
Can you explain why you are using uniqueidentifier in the first place? - that may have some bearing on this.
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
I can think of a couple of cases where this approach might be beneficial.
For the case where neither of the above apply (and you are just materialising into a temp table and getting the same number of nested loops lookups as you would have got without this step and not benefiting from improved cardinality estimates) I would expect this to generally be slower than the original query without the intermediate step (as on the face of it you are doing the same work with some additional overhead added) but haven't tested this.