Indexes are used by queries to find data from tables quickly. Indexes are created on tables and views. Index on a table or a view, is very similar to an index that we find in a book.
If we don’t have an index, and try to look for a specific chapter in the book, then the only way that we have to find the chapter is to go through the pages one by one from the starting page. On, the other hand, if we have the index, we lookup the page number of the chapter in the index, and then we can go directly into that chapter. So, what book index is doing here is that it is drastically reducing the time that it takes for us to find the chapter.
In a similar way, Table and View Indexes, can help the query that we write to find the data quickly. In fact, the existence of the right indexes, can drastically improve the performance of the query.
If there is no index to help the query, then the query engine, checks every row in the table from the beginning to the end. This is called a Table Scan. Table Scan is very bad for performance.
Let’s say at the moment , the Employees table does not have an index on Salary column.
ID |
Name |
Salary |
Gender |
|---|---|---|---|
| 1 | Sam | 2500 | Male |
| 2 | Pam | 6500 | Female |
| 3 | John | 4500 | Male |
| 4 | Sara | 5500 | Female |
| 5 | Todd | 3100 | Male |
SELECT * FROM tblEmployee
WHERE Salary > 5000 and Salary <7000
Now, suppose we are supposed to find all the employees who has greater than 5000 and less than 7000, then the query engine has to check each and every row in the table, in the absence of an index on the Salary column, resulting in a table scan, which can adversely affect the performance, especially if the table is large.
CREATE INDEX IX_tblEmployee_Salary
ON tblEmployee (SALARY ASC)
The above SQL query creates an index which stores salary of each employee, in the ascending order as shown below. The actual index may look slightly different.
Now, when the query engine has to execute the same query, it has an index on the salary column to help this query. Salaries between the range of 5000 and 7000 are usually present at the bottom, since the salaries are arranged in the ascending order. The query engine picks up the row addresses from the index and directly fetch the records from the table, rather than scanning each row in the table. This is called as an Index Seek.
-- DROP INDEX table_name.index_Name
DROP INDEX tbl_Employee.IX_tblEmployee_Salary;
Different types of Indexes available in SQL Server are:
Among these the mostly used index types are Clustered and Non-Clustered
A Clustered Index determines the physical order of data in the table. For this reason, a table can have only one clustered index.
Note: In the above example, Id column is marked as primary key. A Primary Key, constraint creates a clustered index automatically if no clustered index already exists on the table. Therefore, an values inserted into the table are ordered in the sequential order by the Id column.
Although, a table can have only one clustered index. However, the index can contain multiple columns ( a composite index ).
CREATE CLUSTERED INDEX IX_tblEmployee_Gender_Salary
ON tblEmployee(GENDER DESC, Salary ASC)
A Non-Clustered Index is analogous to an index in a textbook. The data is stored in one place, the index in another place. The index will have pointers to the storage location of the data.
Since, the Non-Clustered Index is stored separately from the actual data, a table can have more than one Non-Clustered Index, just like how a book can have an index by Chapters at the beginning and another index by common terms at the end.
In the index itself, the data is stored in an ascending or descending order of the index key, which doesn’t in any way influence the storage of data in the table.
CREATE NONCLUSTERED INDEX IX_tblEmployee_Name ON tblEmployee(Name)
Clustered Index per table, where as we can have more than on Non-Clustered index.Clustered Index is faster than a Non-Clustered Index, because, the Non-Clustered index has to refer back to the table, if the selected column is not present in the index.Clustered Index determines the storage order of rows in the table, and hence does not require additional disk space. But whereas a Non-Clustered Index is stored separately from the table, additional storage space is required.