Many people confuse primary key with a clustered index, or think it's the same thing. This concept is very wrong.
A primary key is a constraint (constraint) that is attached to an index that can be either a clustered index or a nonclustered index.
So having a primary key on a (or a group) field can only mean that he has an index on it, but not necessarily a clustered index.
For example, the following:
Copy Code code 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
In a clustered index, you can have duplicate values. As long as he is not set as the primary key at the same time, but the primary key cannot have duplicate values (whether attached to the clustered index or nonclustered index)
This is emphasized because some people feel that they have a primary key on their table and that there is a clustered index on the table, which is managed in a B-tree way.
If a primary key is not specified as a clustered index, the table may still be managed in a heap, inefficient
--------------------------------------------------------------------------------
about sorting and repeating values：
Sort: When you set up a composite index, you specify multiple fields, so which field is the order of the index?
is sorted by the first field on the index
The sort order of the following index is sorted by the field ID
1 CREATE INDEX TEMPPKNCL_ID_NCL on [dbo]. [TEMPPKNCL] ([Id],[a],[c])
Duplicate value: If the PRIMARY KEY constraint is defined for multiple columns, the values in one column may be duplicated, but any combination of values from all the columns in the PRIMARY KEY constraint definition must be unique.
As shown in the following illustration, the ProductID and VendorID columns in the Purchasing.ProductVendor table Form a composite PRIMARY KEY constraint for this table.
This ensures that the combination of ProductID and VendorID is unique
This means that if a composite primary key is duplicated, the combination of ProductID and VendorID is unique if the ProductID column has a duplicate
The meaning of the words:
(1) The primary key is not a composite primary key
(2) The primary key is established on the ProductID field
(3) ProductID field has duplicate values
Then the primary key establishment will certainly fail
