Clustered and nonclustered index in sql server

Indexes are automatically created when PRIMARY KEY and UNIQUE constraints are defined on table columns.

PRIMARY KEY constraints

1. When you create the PRIMARY KEY constraints on table column, the Database Engine automatically creates clustered index on that column.
2. You cannot create more than one clustered index on table columns.

UNIQUE KEY constraints

1. When you create the UNIQUE KEY constraints on table columns, the Database Engine automatically creates nonclustered index on that column.
2. You can create more than one nonclustered index on table columns.

Note:

You can create the CLUSTERED and NONCLUSTERED index without PRIMARY KEY and UNIQUE constraints on table columns.

Let's explore it

PRIMARY KEY constraints

Step 1:- Create table without any constraints

CREATE TABLE #TestTable

(

    Col1 INT NOT NULL,

    Col2 VARCHAR(10) NULL,

    Col3 VARCHAR(50) NULL

)

Step 2:- Alter table and PRIMARY KEY constraints

ALTER TABLE #TestTable ADD CONSTRAINT PK_COL1 PRIMARY KEY(Col1)

Step 3:- Create the CLUSTERED index on table columns

CREATE CLUSTERED INDEX INDX_TestTable_Col1

ON #TestTable (Col1);

You will get the following error

Msg 1902, Level 16, State 3, Line 48

Cannot create more than one clustered index on table '#TestTable'. Drop the existing clustered index 'PK_COL1' before creating another.

Conclusion

When you created the PRIMARY KEY constraints on table column, the Database Engine automatically created the clustered index on that column.

Step 4:- Drop the PRIMARY KEY constraints

ALTER TABLE #TestTable DROP CONSTRAINT PK_COL1

Step 5:- Create the CLUSTERED index on table columns

CREATE CLUSTERED INDEX INDX_TestTable_Col1

ON #TestTable (Col1);

Conclusion

You noted that you will create the CLUSTERED INDEX without PRIMARY KEY constraints on table columns

Step 6:- Finally DROP the table

DROP TABLE #TestTable

UNIQUE constraints

Step 1:- Create table without any constraints

CREATE TABLE #TestTable

(

    Col1 INT NOT NULL,

    Col2 VARCHAR(10) NULL,

    Col3 VARCHAR(50) NULL

)

Step 2:- Alter table and UNIQUE constraints

ALTER TABLE #TestTable ADD CONSTRAINT UK_COL1 UNIQUE(Col1)

Step 3:- Create the NOTCLUSTERED index on table columns

CREATE NONCLUSTERED INDEX INDX_TestTable_Col1

ON #TestTable (Col1);

CREATE NONCLUSTERED INDEX INDX_TestTable_Col2

ON #TestTable (Col2);

Conclusion

You can create more than one nonclustered index on table columns.

Step 4:- Drop the UNIQUE constraints & INDEX

ALTER TABLE #TestTable DROP CONSTRAINT UK_COL1

DROP INDEX INDX_TestTable_Col1 ON #TestTable

DROP INDEX INDX_TestTable_Col2 ON #TestTable

Step 5:- Create the NONCLUSTERED index on table columns

CREATE NONCLUSTERED INDEX INDX_TestTable_Col1

ON #TestTable (Col1);

Conclusion

You noted that you will create the NONCLUSTERED INDEX without UNIQUE constraints on table columns

Step 6:- Finally DROP the table

DROP TABLE #TestTable