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