Shulou(Shulou.com)05/31 Report--

In this issue, the editor will bring you about the misunderstanding of SQLSERVER clustered index and primary key. The article is rich in content and analyzed and described from a professional point of view. I hope you can get something after reading this article.

Many people confuse Primary Key with clustered indexes, or think it's the same thing. This concept is very wrong. The primary key is a constraint that is attached to an index, which can be either clustered or nonclustered. So having a primary key on a field (or group of fields) only means that it has an index, but it is not necessarily a clustered index.

For example:

The code is as follows:

USE [pratice] GO CREATE TABLE # tempPKCL (ID INT PRIMARY KEY CLUSTERED-clustered index)

USE [pratice] GO CREATE TABLE # tempPKNCL (ID INT PRIMARY KEY NONCLUSTERED-nonclustered index)

DROP TABLE [# tempPKCL] DROP TABLE [# tempPKNCL]

If you do not add the NONCLUSTERED and CLUSTERED keywords, the default is to build a clustered index and a clustered index can have duplicate values. As long as it is not set as the primary key at the same time, but the primary key cannot have duplicate values (whether attached to a clustered index or a nonclustered index) emphasize this because some people think that they have a primary key on their table. they think that there is a clustered index on the table and manage it in the way of a B-tree. If you do not specify that the primary key is a clustered index, the table may still be managed as a heap Inefficient-about sorting and repeating values: sorting: multiple fields are specified when building a composite index So in which field order is the index sorted? Is sorted by the first field on the index

The following index is sorted by 1 CREATE INDEX tempPKNCL_id_ncl ON [dbo]. [tempPKNCL] ([ID], [a], [c]) repeating values: if PRIMARY KEY constraints are defined for multiple columns, the values in one column may be duplicated, but any combination of values from all columns in the PRIMARY KEY constraint definition must be unique. As shown in the following figure, the ProductID and VendorID columns in the Purchasing.ProductVendor table constitute a compound PRIMARY KEY constraint for this table. This ensures that the combination of ProductID and VendorID is the only meaning, that is, if it is a compound primary key, then if there is a repeat in the ProductID column, but the combination of ProductID and VendorID is the only implication:

(1) the primary key is not a compound primary key

(2) the primary key is based on the ProductID field.

(3) if there is a duplicate value in the ProductID field, the establishment of the primary key will certainly fail.

The above is the misunderstanding of the SQLSERVER clustered index and primary key shared by the editor. If you happen to have similar doubts, you might as well refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.