SQL Server: Non-Clustered Indexes
SQL Server provides two types of indexes, clustered and non-clustered indexes. Here you will learn non-clustered indexes.
The non-clustered index does not sort the data rows physically. It creates a separate key-value structure from the table data where the key contains the column values (on which a non-clustered index is declared) and each value contains a pointer to the data row that contains the actual value. It is similar to a textbook having an index at the back of the book with page numbers pointing to the actual information.
There can be 999 non-clustered indexes on a single table is 999.
When you create a Unique constraint, a unique non-clustered index is created on the table.
The syntax for creating a non-clustered index is similar to the clustered index. Just use the keyword "NONCLUSTERED" instead of "CLUSTERED". The following syntax is to create a new non-clustered index on a table.
CREATE NONCLUSTERED INDEX <index_name>
For example, the following creates a non-clustered index on the
Note that the
Employee table has a primary key
EmployeeID so that automatically creates a clustered index.
CREATE NONCLUSTERED INDEX NCI_Employee_Email
Create a Nonclustered Index using SSMS
You can create a non-clustered index using SQL Server Management Studio.
Step 1: Open SSMS. Connect to the database. In Object Explorer, expand the table where you want to create a non-clustered index.
Step 2: Right-click on the
Indexes folder. Point to
New Index and, select
Non-Clustered index.., as shown below.
Step 3: In the
New Index dialog box, on the General page, enter a name for the new index under Index Name.
Step 4: Click on the Add button under the Index Key Columns, as shown below.
Step 5: In the
Select Columns from table dialog box, check the checkbox of the column(s) on which the non-clustered index should be created.
Step 6: Click Ok and save the table.
Thus, you can create non-clustered indexes.