SQL Server clustered index differs from primary key

Source: Internet
Author: User

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.