Non Clustered Index in SQL Server

In my previous article on "Clustered Index in SQL Server" we talked about creating a Clustered Index. In this article we will talk about creating a Non-Clustered Index.

Non Clustered Index:
Non Clustered index is a separate object. This doesn’t depend on how the data is stored in the table. So you can have more the one index on a table.

When you create a primary key on any table by default SQL Server creates the clustered index. To avoid this you need to use NONCLUSTERED keyword in the primary key creation script.

Create a Primary Key with Non Clustered Index:
1) First let’s create a student table
CREATE TABLE STUDENT (
STUDENT_ID INT IDENTITY(1,1),
FIRST_NAME VARCHAR(30),
LAST_NAME VARCHAR(30))

2) Lets define Primary key on STUDENT_ID column
ALTER TABLE STUDENT
ADD CONSTRAINT PK_STUDENT PRIMARY KEY NONCLUSTERED (STUDENT_ID)

Create a Non Clustered Index on Non Primary Key Column:
1) First let’s create a student table
CREATE TABLE STUDENT_EXAM_RESULTS (
STUDENT_ID INT IDENTITY(1,1),
FIRST_NAME VARCHAR(30),
LAST_NAME VARCHAR(30),
RANK INT)

2) Let’s define Non clustered Index on RANK column
CREATE NONCLUSTERED INDEX cidx_rank ON STUDENT_EXAM_RESULTS (RANK)

Where Indexes should be used:
1) Columns often used in WHERE conditions of the query.

Where Indexes should not be used:
1) When the table is very small having less than 400 Records.
Ex: You have a COUNTRY table. This table stores around 150 Countries information. So you don’t need an index on this table in order to access the data.

2) Don’t create the index on the free flow text columns.
Ex: You have one table called PROJECT with Project_Name,Project_Desc columns.
CREATE TABLE PROJECT
( PROJECT_ID INT,
PROJECT_NAME VARCHAR(30),
PROJECT_DESC VARCHAR(300)
)
Don’t create the index on PROJECT_DESC column.

3) Don’t create too many indexes and also don’t duplicate the indexes. These will slowdown your DML operations.
Ex: If you have 30 indexes on a table, when user issues a UPDATE statement, based on the updating column SQL Server has to update all the indexes which depends on this column.