Support us .Net Basics C# SQL ASP.NET Aarvi MVC Slides C# Programs Subscribe Download

Indexes in sql server - Part 35

Why indexes?
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 you don't have an index in a book, and I ask you to locate a specific chapter in that 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 lookup 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 

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 as Table Scan. Table scan is bad for performance.

Index Example: At the moment, the Employees table, does not have an index on SALARY column.

Consider, the following query
Select * from tblEmployee where Salary > 5000 and Salary < 7000

To find all the employees, who has salary greater than 5000 and less than 7000, the query engine has to check each and every row in the table, resulting in a table scan, which can adversely affect the performance, especially if the table is large. Since there is no index, to help the query, the query engine performs an entire table scan.

Now Let's Create the Index to help the query:Here, we are creating an index on Salary column in the employee table
CREATE Index IX_tblEmployee_Salary 
ON tblEmployee (SALARY ASC)

The index stores salary of each employee, in the ascending order as shown below. The actual index may look slightly different.

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 5000 and 7000 are usually present at the bottom, since the salaries are arranged in an 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.

An Index can also be created graphically using SQL Server Management Studio
1. In the Object Explorer, expand the Databases folder and then specific database you are working with.
2. Expand the Tables folder

3. Expand the Table on which you want to create the index
4. Right click on the Indexes folder and select New Index
5. In the New Index dialog box, type in a meaningful name
6. Select the Index Type and specify Unique or Non Unique Index
7. Click the Add
8. Select the columns that you want to add as index key
9 Click OK
10. Save the table

To view the Indexes: In the object explorer, expand Indexes folder. Alternatively use sp_helptext system stored procedure. The following command query returns all the indexes on tblEmployee table.
Execute sp_helptext tblEmployee

To delete or drop the index: When dropping an index, specify the table name as well
Drop Index tblEmployee.IX_tblEmployee_Salary


  1. excellent demonstration

  2. Your instructions are so clear and easy to understand.Thanks.

  3. excellent demonstration. i am very impressed by seeing all this videos. going to make one library for this. very good and very impressive work from you. this will take lots of effort and patience.exceptional work dear..

  4. Your tutorials are so easy to understand. Thanks so much for all your hard work. I loved your text explanation and all the video tutorials.
    To add to this - Indexes can be created on either a single column or a combination of columns in a table and are implemented in the form of B-trees.B- Tree is sorted on the search key and can be searched efficiently!

    1. Thanks very much... for clear & patient explanation

  5. I am very grateful for all the knowledge learned, thank you very much Pragim.

  6. To view the Indexes:
    Execute sp_helpindex tblEmployee

  7. sp_help 'EmployeeDetails' its not sp_helptext

    1. Nagendra , Thanks for rectifying the mistake .

    2. its sp_helpindex.
      Sp_help shows all information but prior shown only about indexes.

  8. Nice tutorial Venkat,very well understood indexes in SQL,Thanks

  9. Great Explanation ,i want to say thank you from the bottom of my heart

  10. I think it Execute sp_helpindex tblEmployee not Execute sp_helptext tblEmployee

  11. Below, please find the SQL Query for creating the tblEmployee table.

    CREATE TABLE tblEmployee
    Salary INT NOT NULL,
    Gender NVARCHAR(10) NOT NULL

    INSERT INTO tblEmployee
    VALUES (1, 'Sam', 2500, 'Male')

    INSERT INTO tblEmployee
    VALUES (2, 'Pam', 6500, 'Female')

    INSERT INTO tblEmployee
    VALUES (3, 'John', 4500, 'Male')

    INSERT INTO tblEmployee
    VALUES (4, 'Sara', 5500, 'Female')

    INSERT INTO tblEmployee
    VALUES (5, 'Todd', 3100, 'Male')

    SELECT * FROM tblEmployee

  12. Your explanations are the best Venkat!

  13. Execute sp_helptext tblEmployee
    you mentioned this one not working instead of these youcan use
    Execute sp_helpIndex tblEmployee

  14. whether there is any drawback in creating the index on PK

  15. Hi Venkat, Very beneficial tutorials provided by you. I appreciate your effort & thank you so much for all your hard work. God bless you

  16. Please edit this..
    Execute sp_helptext tblEmployee to Execute sp_helpindex tblEmployee.

  17. you're the best teacher sir....
    yours all session are best of best...

  18. To view index its
    Execute sp_helpindex tblename
    Its not sp_helptext

  19. anyone who have collated notes bu Kudvenkat?

  20. Hi venkat
    If I need to order salary column as desc or asc how can I do it using graphically


It would be great if you can help share these free resources