CODEWITHSUNDEEP
sqlsql-servert-sqlindexing
Alex Martin
Alex Martin

Reputation: 83

Unique vs NonUnique Clustered Index to speed searches on a NonUnique field

I have a million row dataset for which I regularly join on Column A.

To speed up joining I'm going to create a clustered index on Column A.

Column A is non unique but (Column A, Column B) is a unique pairing.

I will never use Column B in a where clause or join.

Am I better to create a non unique clustered index on just Column A or to create a unique clustered index on (Column A, Column B)?

Upvotes: 2

Views: 105

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271003

You would create a unique index on A,B to enforce uniqueness of the values. This is enforced at the database level, so you will be prevented from inserting duplicate values into the database.

A unique index can be used for resolving queries that need the first columns in the index but not necessarily all of them. So, the unique index is fine for queries on A.

I would say create the unique index. There are two things to keep in mind. The first is if B is a large data type -- like char(500). These values are stored in the index, so including B might make the index rather large.

Second, if the data are not being inserted in A, B order, then making it a clustered index could incur performance overhead on inserts and deletes. New inserts would end up going on a random page, which would likely be filled and then require splitting (or you can use the fill factors of pages to reserve extra space for inserts, at the cost of making the table initially bigger).

Upvotes: 2

Related Questions