SQL Server Indexes with Examples
In this article, I am going to discuss SQL Server Indexes with examples. Indexes in SQL Server can drastically improve the performance of your application if it is used in good manner. On the other hand, it can also negatively affect the performance of your application if you used in the wrong manner. So, as a developer, it is very important to understand the Indexes in SQL Server. As part of this article, we are going to discuss the following pointers.
- How data is stored under a database?
- How will be the database engine retrieves the data from a table?
- Understanding the Full Page Scan and Index Scan.
- What is an index?
- Why we need indexes in SQL Server?
- Multiple Examples to understand SQL Server Indexes.
- When SQL Server uses Indexes?
- Types of indexes in SQL Server.
How data is stored under a database?
The SQL Server stores the data in it under the data pages. A data page is a memory location for storing the information. Generally, a data page has a size of 8KB and every 8 data pages we stored under a logical container known as “extend”.
How will be the database engine retrieves the data from a table?
Whenever the database engine wants to retrieve the data from a database table it will adopt two different mechanisms for searching the data
- Full page scan
- Index Scan
Full Page Scan in SQL Server:
In Full Page Scan, the SQL Server will search for the required information in each and every data page to collect the information. So if the table having more number of rows it will be taking lots of time for searching all the data so it is a time-consuming process.
Index Scan in SQL Server:
In the second case i.e. Index Scan, the SQL Server without searching into each and every data page for retrieving the required information it will make use of an index for retrieving the information, where an index is a pointer to the information what we want to retrieve which can reduce the disk I/O operation saving the time, but if we want to use index scan for searching the data first the index has to be created.
Syntax to Create Index in SQL Server:
CREATE [UNIQUE] [CLUSTERED/ NON-CLUSTERED] INDEX <INDEX NAME> ON <TABLE NAME> (<COLUMN LIST>)
Note: Whenever an index is created on a column or columns of a table internally an index table gets created maintaining the information of a column or columns on which the index is created as well as the address (pointer to the row corresponding to a column).
What is an index in SQL Server?
One of the most important routes to high performance in SQL Server database is an index. It is a database object which is used to speed up the querying process by providing query access to rows in the data table.
When we create an index on any column or columns, then SQL Server internally maintains a separate table called index table. When the user trying to retrieve the data from the existing table, SQL Server checks the index table and then directly go to the original table and retrieve the required data very quickly. If this is not clear at the moment then don’t worry we will discuss this in detail.
Note: In a table, we can use a maximum of 250 indexes. The index type refers to the way the index is stored internally by SQL Server.
Why we need Indexes?
Indexes are used by queries to find data quickly from tables. You can create indexes on tables as well as on views. The Indexes that we created on a table or view are very much similar to the indexes that we find on a book.
For example, if you don’t have an index and if I ask you to locate a specific chapter in the book, then you will have to check each and every page starting from the first page up to the last page of the book. On the other hand, if you have the index, then you can easily find the page number of the chapter in the index and then directly go to that page number to locate the chapter.
So, the book index is helping to drastically reduce the time it takes to find the chapter. In a similar way, table and view Indexes can help the query 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 each and every row in the table from the beginning to the end. This is called a table scan. The table scan is bad for performance.
Let us understand the need for SQL Server Indexes with an example.
We are going to use the following Employee table to understand the concept of SQL Server Indexes.
Please use below SQL Script to create and populate the Employee table with some test data.
CREATE TABLE Employee ( Id INT PRIMARY KEY, Name VARCHAR(50), Salary INT, Gender VARCHAR(10), City VARCHAR(50), Dept VARCHAR(50) ) GO INSERT INTO Employee VALUES (3,'Pranaya', 4500, 'Male', 'New York', 'IT') INSERT INTO Employee VALUES (1,'Anurag', 2500, 'Male', 'London', 'IT') INSERT INTO Employee VALUES (4,'Priyanka', 5500, 'Female', 'Tokiyo', 'HR') INSERT INTO Employee VALUES (5,'Sambit', 3000, 'Male', 'Toronto', 'IT') INSERT INTO Employee VALUES (7,'Preety', 6500, 'Female', 'Mumbai', 'HR') INSERT INTO Employee VALUES (6,'Tarun', 4000, 'Male', 'Delhi', 'IT') INSERT INTO Employee VALUES (2,'Hina', 500, 'Female', 'Sydney', 'HR')
At the moment, the Employee table does not have an index of salary column
SELECT * FROM Employee WHERE Salary > 3500 AND Salary < 7000
To find all the employees whose salary is greater than 3500 and less than 7000 the query engine has to check each and every row present in the table, resulting as a table scan which can affect the performance especially if the table contains a large number of records. Since there is no index on the Salary column of the Employee table to help the query the query engine performs an entire table scan.
How to solve the above problem?
We can solve the above problem by creating an index on the salary column of the Employee table as shown below.
CREATE INDEX IX_Employee_Salary ON Employee(Salary ASC)
Once you execute the above statement the index gets created and you can see the index in the indexes folder which is present inside the Employee table as shown below.
The above index stores the salary of each employee in the ascending order as shown below. The actual index may slightly look difference (Hexadecimal format Row address).
Now, when the SQL Server has to execute the same query, it has an index on the salary column to help this query. Salaries between the range of 3500 and 7000 are usually present at the bottom since the salaries are arranged in ascending order. SQL Server 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 an Index Seek.
To see the index: sp_helpindex Employee
To drop an index: Drop index Employee.IX_Employee_Salary
When SQL Server uses Indexes?
The SQL Server uses indexes of a table provided the select or update or delete statement contained the “WHERE” clause and moreover the where condition column must be an indexed column. If the select statement contains an “ORDER BY” clause then also the indexes can be used.
Note: When SQL Server is searching for information under the database first it verifies the best execution plan for retrieving the data and uses that plan which can be either a full-page scan and index scan.
Types of Indexes in SQL Server:
SQL Server Indexes are divided into two types such as
- Clustered index
- Non- clustered index
In the next article, I am going to discuss the Clustered Index in SQL Server with some examples. Here, in this article, I try to explain the SQL Server Indexes step by step with some examples. I hope you enjoy this article.