Hello Learners. Thank you so much for the support for my first article “Learn About Sql Server Architecture”. I hope you all are doing well.
This article is all about Indexes in SQL Server. Some of us have heard about the term indexes and some of us have not. Well not to worry for those who have not heard about it and are in the learning phase. Let’s just start now.
In simple terms, we can think of dictionary as a SQL Server Engine, the partition by each alphabet (A to Z) as tables and the content page (First Page) as Indexes. Now suppose your first page (Content or Index Page) is missing, and you want to search something starting with ‘G’ in Dictionary (Sql server), you will have to search almost all the pages that are filled with letter ‘G’ in the dictionary. It obviously takes time to find that particular word if there is no first page (Content or Index page). Now suppose you have the First Page (Content or Index Page), this task would be easier as you would know where the page is, where the words that start with letter ‘G’ are, and hence it would take less time to find the word (Record) that you want. Hence, it clearly defines that indexes help in better searches and take less time to fetch the records.
Similarly in SQL Server we have indexes for the tables which help us to fetch the data in less time and help increase the performance as well. There are many types of indexes for different purposes. In this article I will try to include all the indexes with examples as well. But mainly they are categorized as :
- Clustered Indexes
- NonClustered Indexes
Let’s learn about them one by one.
CLUSTERED INDEXES
Clustered indexes are used to sort the data in a particular order. When you create a Primary Key on any Table, it will create a clustered index on that column automatically which helps in sorting the data in the table. In case there is no primary key, you can still make the Clustered Indexes. There can be only ONE Clustered Index per table as the data can only be sorted in any one order on the basis of columns; for instance, if there is an employee table the data can be sorted either by EmpID or by EmpName.
All the indexes are stored in a B Tree Structure format which has a root, intermediate level and leaf node. At all the levels there is a key (Index Column) which helps to sort the data or rows in a table. When the pointer reaches the leaf nodes it has both the key value and the entire row.
A table without a clustered index is called as HEAP and with cluster, it is known as Clustered Table.
Syntax to create Clustered Index
- Create Clustered Index IndexName ON TableName(ColumnName)
Here, I will be using an example of table and then will create a clustered index on it and see the execution plan of the same as well.
- Create Table Test
- (
- ID int,
- Name varchar(20),
- Salary int
- )
Now we will insert the records into the table
- Insert into Test values (6,’f’,23000), (2,’b’,12000), (5,’e’,29000), (3,’c’,56000), (1,’a’,31000), (4,’d’,16000), (8,’h’,21000)
Now we will select the data from the table and see how it appears.
When we run the above query we get the given results.
ID Name Salary
6 f 23000
2 b 12000
5 e 29000
3 c 56000
1 a 31000
4 d 16000
8 h 21000
You will see that the result will be in the order in which they were inserted. Now we will create a clustered index with Name (Key Column) on Test Table.
- Create Clustered Index Ix_CL_Test On Test(Name)
- Select * from Test
When I run the query after creating the Clustered Index you will see that the result will be sorted according to the Name column. Given is the result.
ID Name Salary
1 a 31000
2 b 12000
3 c 56000
4 d 16000
5 e 29000
6 f 23000
8 h 21000
Clean Up
- Drop Index Ix_CL_Test
- Drop Table Test
NONCLUSTERED INDEXES
NonClustered Indexes are saved in a different manner than the Clustered Indexes in B-Tree. The NonClustered Indexes are stored on a B+ Tree. The leaf nodes in a B+Tree consist of the Key Values as well as the pointer to the row data. The pointer (Row Locator) points to the row data if it is a Heap, and to the Clustered Key Value if it is a Clustered Table. This is the reason why NonClustered Indexes are created separately from the Table and hence take up the extra space as well.
NonClustered Indexes are sometimes beneficial in cases where the data is fetched on certain criteria frequently. You can include up to 1023 columns in an NonClustered Index and you can create upto 999 NonClustered Indexes in contrast to 1 Clustered Index per Table.
Read the Difference between B Tree and B+ Tree here : http://www.differencebetween.info/difference-between-b-tree-and-b-plus-tree
Syntax to create NonClustered Index
- Create NonClustered Index IndexName ON TableName(ColumnName)
We will use the given table.
- Create Table Test
- (
- ID int,
- Name varchar(20),
- Salary int
- )
Now we will insert the records into the table
- Insert into Test values (6,’f’,23000), (2,’b’,12000), (5,’e’,29000), (3,’c’,56000), (1,’a’,31000), (4,’d’,16000), (8,’h’,21000)
- Select * From Test
When we run the query we will get the results in the order in which they were inserted.
ID Name Salary
6 f 23000
2 b 12000
5 e 29000
3 c 56000
1 a 31000
4 d 16000
8 h 21000
We will now create a NonClustered Index on Test Table.
- Create NonClustered Index Ix_NCL_Test On Test(Name)
- Select * from Test
You will see no difference as the result set is small, but NonClustered Indexes are very useful when they are used with the Large Tables. Assume the above table has millions of records and you want to read the data; in that case the NonClustered Index will help in increasing the performance.
In the above example the NonClustered Index has the Name Key in B+ Tree and in the leaf nodes a Row Locator pointing to the Table Test.
Other efficient indexes you can create is a Covering Index, which includes all of the columns in the predicate as key column.
- Select Id,Name, Salary from Test Where Name = ‘a’ and Salary > 20000
To satisfy the above query we can make a covering index.
- Create NonClustered Index Ix_Cov_NC_Test On Test(name, salary) Include(ID)
It will help to fetch the data more quickly as the Index has all the columns included. In the coming articles I will discuss Covering Index and Filtered Index as well.
I hope this article will help to have an understanding about the Indexes in SQL Server. If there is any error or wrong information please leave a comment, I will be obliged to rectify it and grow with that. Happy Learning!