SQL Server Index

Index category
Unique index (unique) clustered index (CLUSTERED) nonclustered index (nonclustered)

The difference between a primary key and a unique index

A primary key is a constraint, and a unique index is an index, and the two are essentially different. The primary key must be created with a unique index, and the uniqueness index is not necessarily the primary key. The uniqueness index column allows null values, while primary key columns are not allowed to be null. When the primary key column is created, it is already default to a null value + unique index. A primary key can be referenced by another table as a foreign key, and a unique index cannot. A table can create a maximum of one primary key, but multiple unique indexes may be created. Primary keys are more suitable for unique identities that are not easily changed, such as auto-increment columns, Social Security numbers, and so on. In RBO mode, the primary key's execution plan priority is higher than the unique index. Both can improve the speed of queries.

-- Create a table that contains only primary keys and unique indexes

CREATE TABLE Test

(PrimaryKey VARCHAR2 (20),

UniqueKey VARCHAR2 (20)

);

-- Create a primary key and a unique index, respectively, with different syntax

ALTER TABLE test ADD CONSTRAINT test_primarykey PRIMARY KEY (PrimaryKey);

CREATE UNIQUE INDEX test_uniquekey on test (UniqueKey);

--Two index names can be seen in user_indexes

SELECT table_name,table_type,index_name,index_type,uniqueness

From User_indexes

WHERE table_name= ' TEST ';

--You can see two index field names in User_ind_columns

SELECT table_name,index_name,column_name,column_position

From User_ind_columns

WHERE table_name= ' TEST ';

--Only the PRIMARY KEY constraint name can be seen in user_constraints

SELECT Table_name,constraint_name,constraint_type

From User_constraints

WHERE table_name= ' TEST ';

---only the PRIMARY KEY constraint field name can be seen in User_cons_columns

SELECT table_name,constraint_name,column_name,position

From User_cons_columns

WHERE constraint_name in (SELECT constraint_name

From User_constraints

WHERE table_name= ' TEST ');

--Adds a non-null constraint to a unique index

ALTER TABLE test MODIFY uniquekey not NULL;

---only the PRIMARY KEY constraint name and the non-null constraint name can be seen in user_constraints

SELECT Table_name,constraint_name,constraint_type

From User_constraints

WHERE table_name= ' TEST '

---only the PRIMARY KEY constraint field name and the non-null constraint field name can be seen in User_cons_columns

SELECT table_name,constraint_name,column_name,position

From User_cons_columns

WHERE constraint_name in (SELECT constraint_name

From User_constraints

WHERE table_name= ' TEST ')

SQL Server Index