I would like compare the SQL Indexes with indexes in the text book. In the text book, if we search for the particular topic, would we search by traverse through page by page and find the topic. Definitely not, unless we do have enough time going through all the pages. So, What will we do? . Go the the indexes of the book, find the topic and directly go the page based on the page number. In the similar fashion, SQL would the maintain the indexes for the table, help us to get to the record quickly.
So, Indexes will help to quickly access the data rather than scanning through row by row. Hence, it will help us to improve the performance of the operation.
In this article, we will go through the types of indexes in SQL Server and its pros and cons.
There are two types of Index.
- Clustered Index
- Non Clustered Index
Clustered Index
A clustered index is a index where data is sorted and stored in the table and it is depend on the key values.It is physically stored and when it is applied to the column in a table, make sure that column has contains unique value and incurs less modification.
I can relate it to a dictionary where all the words are sorted alphabetically.
Data is stored at a leaf level of B tree in a clustered index.
Note: Clustered Index can be applied to only one column in a table. And most like the primary key
Non clustered Index
A non clustered index is similar to clustered index but it wont store data physically. We can create upto 999 non clustered indexes on a table. Here, data will be displayed in random order but logical ordering is applied. It will be stored differently with in a table,that contains the columns used for indexing and pointer that refer to the row in the table. Basically, it suits for columns which change frequently.
I can relate it to a text book where all the keywords are mentioned in the last page of book and reference to page number where it is used.
Note: By default, when index is created, it is treated as non-clustered index.
Syntax to create an index.
CREATE [UNIQUE] {CLUSTERED | NONCLUSTERED} INDEX Index_name ON
{Table_name | View_name} (column(s))
- UNIQUE – Determines the mentioned columns in the table/view are unique
- CLUSTERED – Determines the index is clustered index
- NONCLUSTERED – Determines the index is non clustered index
- Table_name or View_name – Determines the name of the table or view where the index is created
- Column_Name – Determines the name of the columns where the index is applied to
Example
Clustered Index
Non Clustered Index
Side Effects
Though it has advantage of improve the performance by quick lookup the data. On the flip side, it has got side effects on the cost of INSERT, UPDATE and DELETE statements. When it is executed and the table contains indexes, it will alter the data in the table.
Let us think about INSERT statement, if we add any new record to a table and if it contains the clustered index means, it will rearrange the the data according to the clustered index column. So it will incur extra cost every time when add new record. So, we should keep that in mind when we design a new table with indexes.
SCAN & SEEK
A table without clustered index called HEAP table. The data in the table is not sorted. So, the SQL SERVER will scan the whole table to find the particular data. That process is called SCAN.
A clustered index will sort store key value (column) of table. So, when SQL SERVER look for any data. It will traverse from root to leaf of the B-Tree to find a data. This is a fast approach and this process is called SEEK.
Hope this article outlines the basics of Clustered and non clustered index with its benefits and side effects.
Happy SQL’ing 🙂
My coding exploration 