Basic SQL Server Index knowledge (4) -- primary key and clustered index from Jun. Net)

Some people may be confused with primary keys and clustered indexes. In fact, these two concepts are different. The following is a simple description. If you do not want to look at the context text, you can directly look at the comparison between the two. Especially the comparison of the last item.

Primary Key)

Description from msdn:

A table usually has a column or a group of columns that uniquely identify the values of each row in the table. Such a column or multiple columns are called the primary key of the table, which is used to force the Object Integrity of the table. When creating or modifying a table, you can create a primary key by defining the primary key constraint.

A table can have only one primary key constraint, and columns in the primary key constraint cannot accept null values. Because the primary key constraint ensures data uniqueness, this constraint is often defined for the ID column.

If the primary key constraint is specified for the table, the SQL Server 2005 database engine creates a unique index for the primary key column to force data uniqueness. When a primary key is used in a query, this index can also be used to quickly access data. Therefore, the selected primary key must comply with the rules for creating a unique index.

When a primary key is created, the database engine automatically creates a unique index to enforce the uniqueness requirements of the primary key constraint. If no clustered index exists in the table or the non-clustered index is not explicitly specified, a unique clustered index is created to enforce the primary key constraint.

Clustered Index

The clustered index sorts and stores the data rows based on the key values of data rows in the table. Each table can have only one clustered index, because data rows can only be stored in one order.

Almost every table defines clustered indexes for columns to implement the following functions:

• It can be used for frequently used queries.
• Provides high uniqueness.

Comparison between the two

Below is a simple comparison table

	Primary Key	Clustered Index
Purpose	Forced table entity integrity	Sorts data rows for easy Query
Number of tables in a table	A table has at most one primary key.	A table can have at most one clustered index.
Can multiple fields be defined?	One primary key can be defined by multiple fields	One index can be defined by multiple fields.
Whether NULL data rows are allowed	If the data in the data column to be created is null, a primary key cannot be created. The specified primary key constraint column during table creation is implicitly converted to not null.	Columns that do not limit the creation of clustered indexes must not be null. That is, the column data can be null. See the last comparison.
Must the data be unique?	Data must be unique	Data can be unique or not unique. See the unique settings you have defined for this index. (In this case, we need to look at the following comparison. Although your data columns may not be unique, the system will generate a unique column that you cannot see for you)
Created Logic	When the database creates a primary key, it automatically creates a unique index. If no clustered index exists in the table and the non-clustered index is not specified when the primary key is created, a unique clustered index is created when the primary key is created.	If the unique attribute is not used to create a clustered index, the database engine automatically adds a four-byte uniqueifier column to the table. If necessary, the database engine automatically adds a uniqueifier value to the row to make each key unique. This column and column value are used internally and cannot be viewed or accessed by users.

Refer:

The following post strongly replies:
Http://topic.csdn.net/t/20021212/16/1255429.html