Misunderstanding of SQL Server clustered index and primary key

Many people may confuse PrimaryKey with 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. So on one (or a group) Field

Many people may confuse Primary Key with 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. So on one (or a group) Field

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 GO12 create table # tempPKNCL13 (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 INDEX tempPKNCL_id_ncl ON [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.