Скачать или смотреть The Importance of Using a Unique Non-Clustered Index on a Primary Key in SQL Server

Discover why defining a `UNIQUE` non-clustered index on a primary key can enhance SQL Server performance and the considerations involved in index design.
---
This video is based on the question https://stackoverflow.com/q/62944322/ asked by the user 'Marcio Gabe' ( https://stackoverflow.com/u/13944574/ ) and on the answer https://stackoverflow.com/a/62944499/ provided by the user 'David Browne - Microsoft' ( https://stackoverflow.com/u/7297700/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: Non clustered index on the same column as the clustered primary key should be unique?

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
Understanding Indexes in SQL Server: Should You Use a Unique Non-Clustered Index?

When working with large databases in SQL Server, efficient query performance is paramount. One area that often raises questions among database administrators and developers is the use of indexes, particularly when it comes to managing primary keys and additional indexes. A common scenario involves deciding whether a non-clustered index on the same column as the clustered primary key should be defined as unique. This guide will explore this topic through a practical case study, providing insights and best practices.

The Problem

Let's consider a real-world scenario: you have a People table where PersonID serves as the clustered primary key. This table has numerous additional columns, but frequently you need to query only the PersonID to retrieve a person's name. To optimize performance, you've added a non-clustered index on the PersonID with the Name column included. The main questions that arise from this decision include:

Should the non-clustered index be defined as unique?

Does this help SQL Server in any way?

Is it necessary to maintain PersonID as the clustered index?

Can two indexes exist on the same column simultaneously?

Should the clustered index be modified to reflect another column?

Solution Breakdown

Should You Make the Non-Clustered Index Unique?

Yes, you should define your non-clustered index on PersonID as unique. Since PersonID is already a primary key that guarantees unique values, marking the index as unique reinforces this aspect, although it won’t affect the implementation significantly. SQL Server recognizes that PersonID serves as the row locator for this table and treats it accordingly in the index.

Does Defining It as Unique Help SQL Server?

In practice, it makes no difference to SQL Server performance if the index is defined as unique or not, as PersonID already serves as the key column for the underlying clustered index. The primary rationale for declaring it as unique is mainly for clarity and to adhere to best practices rather than performance enhancement.

Do You Still Need the Clustered Index on PersonID?

Yes, maintaining a clustered index on PersonID is advisable. This index organizes the physical storage of the table's data in a way that allows for quick access based directly on PersonID. If you ever decide to change this clustered index to another column, you would need to ensure that any non-clustered index on PersonID is defined as unique, since it will only contain distinct values.

Is It a Problem to Have Two Indexes on the Same Column?

No, it is not a problem to have two indexes on the same column. In fact, having a non-clustered index on PersonID can help optimize performance during specific queries. While the clustered index includes all columns in the row, the separate non-clustered index offers a lightweight alternative. This can be particularly useful for counting rows, paging, and handling queries that only require the PersonID and Name.

Should the Clustered Index Be Changed?

While it's technically feasible to have a clustered index on a different column, it’s generally optimal to keep PersonID as the clustered index. This choice allows SQL Server to maintain an efficient structure, especially since this column is inherently unique and often utilized in joins.

Conclusion

Navigating the world of indexes can be challenging, but by understanding the role of unique non-clustered indexes on primary keys, you can greatly enhance your database performance. By maintaining PersonID both as a primary key and a non-clustered, unique index, you’ll ensure efficient data retrieval while leveragi