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:
Table A
has the following columns:RecordID
,QuestionID
,Pts
,PtsOf
and many other.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?