Shulou(Shulou.com)05/31 Report--

This article mainly explains "what is the difference between primary key and clustered index". The explanation in this article is simple and clear, easy to learn and understand. Please follow the idea of Xiaobian and go deep into it slowly to study and learn "what is the difference between primary key and clustered index" together!

PRIMARY KEY

Description from MSDN:

A table usually has a column or set of columns that contain values that uniquely identify each row in the table. Such a column or columns is called the primary key (PK) of the table and is used to enforce the entity integrity of the table. When creating or modifying tables, you can create primary keys by defining PRIMARY KEY constraints.

A table can have only one PRIMARY KEY constraint, and columns in PRIMARY KEY constraints cannot accept null values. PRIMARY KEY constraints are often defined on identity columns because they guarantee data uniqueness.

If a PRIMARY KEY constraint is specified for a table, the SQL Server 2005 Database Engine enforces uniqueness of the data by creating unique indexes for the primary key columns. This index can also be used for quick access to data when primary keys are used in queries. Therefore, the selected primary key must obey the rules for creating unique indexes.

When a primary key is created, the database engine automatically creates a unique index to enforce the uniqueness requirement of the PRIMARY KEY constraint. If a clustered index does not exist on the table or a nonclustered index is not explicitly specified, a unique clustered index is created to enforce the PRIMARY KEY constraint.

clustered index

A clustered index sorts and stores rows of data within a table based on their key values. There can be only one clustered index per table because the rows themselves can only be stored in one order.

Almost every table defines clustered indexes on columns to do the following:

Can be used for frequently used queries.

Provides high uniqueness.

the comparison

Here is a simple comparison table

Primary key clustered index Purpose Enforce the entity integrity of the table Sort the data rows to facilitate queries How many tables are used in a table Up to one primary key A table Up to one clustered index Whether multiple fields are allowed to define a primary key Multiple fields are allowed to define an index Multiple fields are allowed to define whether null data rows are allowed If null data exists in the data column to be created, the primary key cannot be established.

PRIMARY KEY constraint columns specified when creating tables are implicitly converted to NOT NULL. Columns that do not have a clustered index must not be null .

In other words, the data that can be listed is null.

See the last item Whether the comparison requires data to be unique Data may or may not be unique. See the UNIQUE settings you define for this index.

(This point needs to see a comparison later, although your data column may not be unique, but the system will generate a unique column for you that you cannot see) The logical database created will automatically create a unique index at the same time as the primary key is created.

If the table does not have a clustered index before and a non-clustered index is not enforced when the primary key is created, a unique clustered index is created when the primary key is created. If the clustered index is not created using the UNIQUE attribute, the database engine automatically adds a four-byte uniquifier column to the table.

If necessary, the database engine automatically adds a uniquifier value to the row to make each key unique. This column and column values are for internal use and cannot be viewed or accessed by users. Thank you for reading, the above is "what is the difference between primary key and clustered index" content, after the study of this article, I believe we have a deeper understanding of the difference between primary key and clustered index, the specific use of the situation still needs to be verified by practice. Here is, Xiaobian will push more articles related to knowledge points for everyone, welcome to pay attention!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.