Many people may mix the Primary Key and clustered index, or think this is the same thing. This concept is very incorrect.
A primary key is a constraint attached to an index. This index can be a clustered index or a non-clustered index.
Therefore, if a primary key exists on one (or a group) field, it indicates that it has an index, but not necessarily a clustered index.
For example:Copy codeThe 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 -- Non-clustered Index
)
Drop table [# tempPKCL]
Drop table [# tempPKNCL]
If the NONCLUSTERED and CLUSTERED keywords are not added, the CLUSTERED index is created by default.
Duplicate values can exist in a clustered index. 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 clustered index or non-clustered index)
This is emphasized because some people think that they have set a primary key on their tables, so they think that the tables have clustered indexes and are managed in B-tree mode.
If the primary key is not specified as a clustered index, tables may still be managed in a heap mode, resulting in low efficiency.
--------------------------------------------------------------------------------
Sorting and repeated values:
Sorting: multiple fields are specified when a composite index is created. Which field is used for sorting the index order?
Is sorted by the first field on the index
The following index is sorted by the ID field.
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 the column may be repeated, but any combination of values from all columns in the primary key constraint definition must be unique.
As shown in, the ProductID and VendorID columns in the Purchasing. ProductVendor table constitute a composite primary key constraint for this table.
This ensures that the combination of ProductID and VendorID is unique.
This means that if it is a composite primary key, if the ProductID column is repeated, but the combination of ProductID and VendorID is unique
Implication:
(1) The primary key is not a composite primary key.
(2) The primary key is created on the ProductID field.
(3) Duplicate ProductID Fields
Then the primary key creation will certainly fail.