SQL has three types of indexes, unique indexes cannot have duplicates, but clustered indexes, nonclustered indexes can have duplicates

Important:

(1) If SQL is created without specifying a type then the default nonclustered index

(2) Clustered and nonclustered indexes can have duplicate records, and unique indexes cannot have duplicate records.

(3) The primary key defaults to a clustered index with a unique constraint, but it can also be specified as a nonclustered index for a unique constraint when the primary key is created, so the primary key is simply a clustered index with a unique constraint, not a primary key, which is a clustered index with a unique constraint

A bit of a mouthful, you can refer to my blog: The primary key is the clustered index?

Creating an index for a column is actually a sort of column for easy querying. The index of a column is set to a sort of column.

The primary key is unique, so a primary key is created and a unique index is created for that field.

A unique index is essentially a requirement that all data in the specified column must be different.

The difference between the primary key and a unique index:

There can be only one primary key for 11 tables, and a unique index may build multiple.
The 2 primary key can be used as a foreign key for other tables.
3 The primary key is not nullable, and the unique index can be null.

Clustered index: A directory in which data within a table is arranged according to certain rules. Because of this, there is only one focus index in a table. To this we should note that the "primary key is the focus index" is extremely wrong, is a focus on the index of a waste. (although the SQL Server default primary key is the focused index) the biggest benefit of using a focused index is to quickly narrow the query to the query requirements and avoid full table scans. Next, a field with a different number of fields as a focused index does not conform to the principle that a clustered index should not be established under a large number of cases.

I. Role of the Index

1, help to retrieve data;

2, improve the connection efficiency;

3, save the Order by, GROUP by time;

4. Ensure data uniqueness (unique index only).

Second, the design of the index

When determining whether to build an index, we first determine whether it is clustered or nonclustered, single or multi-column, unique or non-unique, whether the column is ascending or descending, and how it is stored, such as partition, fill factor, and so on. Here's a look:

1. Clustered index

(1) First point out a misunderstanding, the primary key is not necessarily a clustered index, but in SQL Server, not explicitly stated in the case of default, the primary key is defined as clustered, and Oracle is the default nonclustered, because the ROWID in SQL Server is not open to use.

(2) A clustered index is suitable for a column that needs to be scoped, because the leaf node of the clustered index holds an ordered row of data, and the query engine can directly locate the leaf nodes on both ends according to the range given in the where, and the data of this part of the node page can be taken out according to the list order;

(3) The clustered index should be built on the column that the value does not change, otherwise it will cause the non-clustered index maintenance;

(4) Try to establish a clustered index before the nonclustered index is established, otherwise it will cause the rebuilding of all nonclustered indexes on the table;

(5) The clustered index should avoid being built on a column of numerical value, otherwise it may cause the competition of IO and the imbalance of B-tree, which causes the database system to maintain the balance of B-tree frequently. The column values of a clustered index are best to be evenly distributed across the table.

3. Unique index

(1) to point out another misunderstanding, the clustered index is not necessarily a unique index , because SQL Server defines the primary key as a clustered index by default, in fact, whether the index is unique or not is not related to aggregation, the clustered index can be a unique index, or it can be a non-unique index;

(2) Set the index to be unique, for the equivalent lookup is very advantageous, when the first qualifying record can stop the search, return data, and not a unique index to continue to find, again, because the need to ensure uniqueness, each row of data insertion will check the repeatability;

The following is a simple comparison table

	Primary key	Clustered index
Use	Enforce the entity integrity of a table	The sorting of data rows is convenient for querying
How many of a table?	One table with up to one primary key	One table at most one clustered index
Whether to allow multiple fields to define	A primary key can be defined by multiple fields	An index can be defined by multiple fields
Whether to allow null data rows to appear	The primary key cannot be established if there is null in the data column to be created. The PRIMARY KEY constraint column specified when the table was created is implicitly converted to not NULL.	Columns that do not restrict the creation of a clustered index must not be null. That is, the data that can be listed is null See the last comparison
Whether the required data must be unique	Requirement data must be unique	The data can be unique or not unique. Look at the UNIQUE settings you define for this index. (This needs to look at the next comparison, although your data column may not be unique, but the system will create a unique column for you to see)
The Created logic	A database creates a primary key at the same time that a unique index is created automatically. If the table does not have a clustered index before it is established and the primary key is not forced to use a nonclustered index at the same time, the primary key is established and a unique clustered index is established	If you do not use the UNIQUE attribute to create a clustered index, the database engine automatically adds a four-byte uniqueifier column to the table. When necessary, the database engine automatically adds a Uniqueifier value to the row so that each key is unique. This column and column values are for internal use and cannot be viewed or accessed by the user.

Reference: Primary key, unique index relationship for clustered index

SQL has three types of indexes, unique indexes cannot have duplicates, but clustered indexes, nonclustered indexes can have duplicates