SQL Server clustered index differs from primary key

The primary key is a constraint (constraint), which is attached to an index that can be either a clustered index or a nonclustered index.

So there is a primary key on a (or a set) of fields that can only indicate that he has an index on it, but not necessarily a clustered index.

For example, the following:

1 use [Pratice] 2 GO 3 CREATE TABLE #tempPKCL 4 (  5   ID INT PRIMARY KEY CLUSTERED  --Clustered index 6) 7  8  9---- -----------------------------Use [pratice]11 GO12 CREATE TABLE #tempPKNCL13 (   ID INT PRIMARY KEY nonclustered
   --Nonclustered index (non-clustered) [#tempPKCL]19 drop table [#tempPKNCL]

If you do not add the nonclustered and CLUSTERED keywords, the clustered index is built by default

In a clustered index, there can be duplicate values. As long as he is not set as the primary key, but the primary key cannot have duplicate values (whether attached to a clustered index or a nonclustered index)

This is emphasized because some people feel that they have a primary key set on their table and that they have a clustered index on the table, which is managed by the B-tree.

If the 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 create a composite index, you specify multiple fields, which is the order in which the index order is sorted?

is sorted by the first field on the index

The sort order for this 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 on 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, the ProductID and VendorID columns in the Purchasing.ProductVendor table make up the composite PRIMARY KEY constraint for this table.

This ensures that the combination of ProductID and VendorID is unique.

This means that if there is a composite primary key, then if the ProductID column has duplicates, the combination of ProductID and VendorID is unique.

The meaning of the words:

(1) 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 definitely fail.

SQL Server clustered index differs from primary key