Misunderstanding of SQL Server clustered index and primary key
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:
1 Use [ Pratice ] 2 Go 3 Create Table # Temppkcl 4 ( 5 ID Int Primary Key Clustered -- Clustered Index 6 ) 7 8 9 -- ------------------------------- 10 Use [ Pratice ] 11 Go 12 Create Table # Temppkncl 13 ( 14 ID Int Primary Key Nonclustered -- Non-clustered Index 15 ) 16 17 18 Drop Table [ # Temppkcl ] 19 Drop Table [ # Temppkncl ]
If you do not addNonclusteredAndClusteredKeyword. 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:
Sort: When a composite index is created, multiple fields are specified. In which order is the index ordered?
Is sorted by the first field on the index
The following index is sorted by the ID field.
1 Create IndexTemppkncl_id_nclOn [DBO].[Temppkncl]([ID],[A],[C])
Repeated 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.