SQL Server Indexes
In this article, I am going to SQL Server Indexes with examples. But before understanding SQL Server Indexes let us understand some key points about SQL Server which is very important to understand 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 of the information. Generally, a data page having 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 information 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
In the first case i.e. 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.
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.
The syntax for creating 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 address (pointer to the row corresponding to a column).
What is an index?
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.
By using indexes we can save time and we can improve the performance of database queries and applications.
An index contains keys built from one or more columns in the table and maps to the storage location of the specified data.
When we create an index on any column SQL Server internally maintains separate table called index table so that when any user trying to retrieve the data from existing table depends on index table SQL Server directly go to the table and retrieve required data very quickly.
In a table, we can use a maximum 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 from tables quickly. The Indexes are created on tables and views. Indexes on a table or a view are very similar to an index that we find in a book.
If you don’t have an index and I ask you to locate a specific chapter in the book, you will have to look at every page starting from the first page of the book.
On the other hand, if you have the index, you look up the page number of the chapter in the index and then directly go to that page number to locate the chapter.
Obviously, 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 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. The table scan is bad for performance.
Generally, a library has a huge collection of books, files, etc. A student requests the librarian for a book of Microsoft SQL Server 2012. If we think without an index the librarian had to find this without any help then he/she has to search one by one for that particular book.
This must be a time-consuming process so with a proper arrangement that is with the help of an index it is very much easier and faster to find out the desired book.
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 as Index Seek.
To see the index
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
SQL Server Indexes are divided into two types such as
- Clustered index
- Non- clustered index
In the next article, I will discuss the Clustered Index in SQL Server with some examples.
In this article, I try to explain the SQL Server Indexes step by step with some examples. I hope this article will help you with your need. I would like to have your feedback. Please post your feedback, question, or comments about this article.