Indexes are used to speed-up query process in SQL Server, resulting in high performance. They are similar to textbook indexes. In textbooks, if you need to go to a particular chapter, you go to the index, find the page number of the chapter and go directly to that page. Without indexes, the process of finding your desired chapter would have been very slow.

These applies to indexes in database tool. Without an index, a DBMS has to iterate through each record in the table in order to to select the desired results.

This process is called table-scanning and is very slow. If you crate indexes, the database got to the index first and then retrieves the corresponding table records directly.

There are tow types od Indexes in SQL-Server

1. Clustered Index
2. Non-Clustered Index

Clustered Index

A clustered index defines the order in which data is physically stored in a table. Table data can be sorted in only way, therefore, there can be only one clustered index per table. In SQL Server, the primary key constraint automatically creates a clustered index on that particular column.

Let’s take a look. First, create a member table inside your db by executing the following script:

          
CREATE TABLE member
(
    id INT,
    name VARCHAR(50) NOT NULL,
    gender VARCHAR(50) NOT NULL,
    DOB datetime NOT NULL,
    total_score INT NOT NULL,
    city VARCHAR(50) NOT NULL
    CONSTRAINT PK_member_Id PRIMARY KEY (Id)   
 )

Notice here in the member table we have set primary key constraint on the id column. This automatically creates a clustered index on the id column. To see all the indexes on a particular table execute sp_helpindex stored procedure. This stored procedure accepts the name of the table as a parameter and retrieves all the indexes of the table. The following query retrieves the indexes created on student table.

EXECUTE sp_helpindex member

This query will return this reult:

It will shown us only one index. The primary key that we created on the id column.

This clustered index stores the record in the member table in the ascending order of the id. Therefore, if the inserted record has the id of 5, the record will be inserted in the 5th row of the table instead of the first row. Similarly, if the fourth record has an id of 3, it will be inserted in the third row instead of the fourth row. This is because the clustered index has to maintain the physical order of the stored records according to the indexed column i.e. id. To see this ordering in action, execute the following script:

      
INSERT INTO member

VALUES  
(6, 'Kate', 'Female', '03-JAN-1985', 500, 'Liverpool'), 
(2, 'Jon', 'Male', '02-FEB-1974', 545, 'Manchester'),
(9, 'Wise', 'Male', '11-NOV-1987', 499, 'Manchester'), 
(3, 'Sara', 'Female', '07-MAR-1988', 600, 'Leeds'), 
(1, 'Jolly', 'Female', '12-JUN-1989', 500, 'London'),
(4, 'Laura', 'Female', '22-DEC-1981', 400, 'Liverpool'),
(7, 'Joseph', 'Male', '09-APR-1982', 643, 'London'),  
(5, 'Alan', 'Male', '29-JUL-1993', 500, 'London'), 
(8, 'Mice', 'Male', '16-AUG-1974', 543, 'Liverpool'),
(10, 'Elis', 'Female', '28-OCT-1990', 400, 'Leeds');

The above script inserts ten records in the member table. Notice here the records are inserted in random order of the values in the id column. But because of the default clustered index on the id column (our primary key), the records are physically stored in the ascending order of the values in the id column. Now let's execute the following SELECT statement to retrieve the records from the student table.

select * from member

you will get this result:

Creating non Clustered index

You can create your own custom index as well the default clustered index. To create a new clustered index on a table you first have to delete the previous index:

ALTER TABLE member DROP CONSTRAINT PK_member_Id

Now create the new clustered index by this script

CREATE CLUSTERED INDEX IX_member_Gender_Score ON member(gender ASC, total_score DESC)

The process of creating clustered index is similar to a normal index with one little exception. With clustered index, you have to use the keyword CLUSTERED before INDEX.

The above script creates a clustered index named IX_member_Gender_Score on the student table. This index is created on the “gender” and “total_score” columns. An index that is created on more than one column is called “composite index”.

| It will be nice to name the index to what it is related to.

The above index first sorts all the records in the ascending order of the gender. If gender is same for two or more records, the records are sorted in the descending order of the values in their total_score column. You can create a clustered index on a single column as well. Now if you select all the records from the member table, they will be retrieved in the following order:

Non Clustered Indexes

A non-clustered index doesn’t sort the physical data inside the table. In fact, a non-clustered index is stored at one place and table data is stored in another place. This is similar to a textbook where the book content is located in one place and the index is located in another (at the end of the book). This allows for more than one non-clustered index per table.

When a query is issued against a column on which the index is created, the database will first go to the index and look for the address of the corresponding row in the table. It will then go to that row address and fetch other column values. It is due to this additional step that non-clustered indexes are slower than clustered indexes.

Creating Non Clustered Indexes

Ths syntax for a clustered ist similar to the clustered index. Instead of the clustered Keyword, you must use the nonclustered Keyword.

Lets create a non clustered index on the name column:

CREATE NONCLUSTERED INDEX IX_member_Name ON member(name ASC)

This will create nonclustered index on the name column of the member table. The index sorts the name in ascending order. As we said earlier, the table data and index will be stored in different places. The table records will be sorted by a clustered index if there is one. The index will be sorted according to its definition and will be stored separately from the table. The table records will be sorted by a clustered index if there is one. The index will be sorted according to its definition and will be stored separately from the table.

Notice, here in the index every row has a column that stores the address of the row to which the name belongs. So if a query is issued to retrieve the gender and DOB of the member named Jon, the database will first search the name Jon inside the index. It will then read the row address of Jon and will go directly to that row in the member table to fetch gender and DOB of Jon.

Conclusion

From the discussion we find following differences between clustered and non-clustered indexes.

1. There can be only one clustered index per table. However, you can create multiple non-clustered indexes on a single table.
2. Clustered indexes only sort tables. Therefore, they do not consume extra storage. Non-clustered indexes are stored in a separate place from the actual table claiming more storage space.
3. Clustered indexes are faster than non-clustered indexes since they don’t involve any extra lookup step.