CODEWITHSUNDEEP
sqlsql-serverindexing
Derrick Moeller
Derrick Moeller

Reputation: 4950

SQL: Add Primary Key to Non-Unique Index

Let's say a query is filtering on two fields and returning primary key values.

SELECT RowIdentifier
FROM Table
WHERE QualifierA = 'exampleA' AND QualifierB = 'exampleB'

Assuming the clustered index is not the PrimaryKey would a non-unique index that contains QualifierA and QualiferB be best served via the addition of the RowIdentifier(Scenario A & Scenario B). Or would it be more appropriate to simply include it(Scenario C)?

Scenario A: Non-Unique, Non-Clustered

CREATE NONCLUSTERED INDEX IX_Table_QualifierA
ON [dbo].[Table] ([QualifierA],[QualifierB],[RowIdentifier])

Scenario B: Unique, Non-Clustered

CREATE UNIQUE NONCLUSTERED INDEX IX_Table_QualifierA
ON [dbo].[Table] ([QualifierA],[QualifierB],[RowIdentifier])

Scenario C:

CREATE NONCLUSTERED INDEX IX_Table_QualifierA
ON [dbo].[Table] ([QualifierA],[QualifierB])
INCLUDE ([RowIdentifier])

Finally I'm assuming that if the PrimaryKey were the clustered index that neither is necessary, is this accurate?

Upvotes: 1

Views: 2680

Answers (2)

Brandon
Brandon

Reputation: 702

If there is a CLUSTERED index, it is automatically included in all indexes on the table. You can explicitly include it but it is not required.

The UNIQUE index simply enforces uniqueness. The PK should already have this constraint. You do not need to re-enforce it in every index.

If you are including the PK in your where clause, it will almost certainly use the PK index to find that row because it is guaranteed to return the fewest results, so including in your index gains you nothing for lookups. It could also potentially skew the cardinality engine and make SQL think the index is more distinct than it really is.

For the above reasons, I would select Option C

CREATE NONCLUSTERED INDEX IX_Table_QualifierA
ON [dbo].[Table] ([QualifierA],[QualifierB])
INCLUDE ([RowIdentifier])

I would use this regardless of what column is clustered. This will give you the performance, insure the index will continue to perform regardless of the CLUSTERED INDEX, and make it explicit what the index is used for.

Upvotes: 1

Scott T
Scott T

Reputation: 283

I'm wondering what's more appropriate? A non-clustered unique index incorporating all three fields, or a non-clustered non-unique index incorporating just the two fields(QualifierA & QualifierB) but including the PrimaryKey.

There's a third option. A non-clustered, non-unique index incorporating all three fields.

When you make an index, the fields in the index are duplicated to another place in memory so the server can go after those fields with ease. If you only have QualiferA and Qualifier B in the index it will find the rows in that index that meet your criteria and then go back to the main table to pick up the RowIdentifier. Instead, include all three in there to improve performance.

Remember, make sure you put QualifierA and QualifierB before RowIdentifier in your index. The order of the columns determine how the data is ordered.

Try it out with some test data if you like, and look at the query plan to see what it's doing.

Upvotes: 0

Related Questions