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 
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 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

24 comments:

  1. excellent demonstration

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

    ReplyDelete
  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..

    ReplyDelete
  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!

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

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

    ReplyDelete
  6. To view the Indexes:
    Execute sp_helpindex tblEmployee

    ReplyDelete
  7. sp_help 'EmployeeDetails' its not sp_helptext

    ReplyDelete
    Replies
    1. Nagendra , Thanks for rectifying the mistake .

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

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

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

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

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

    CREATE TABLE tblEmployee
    (
    Id INT PRIMARY KEY NOT NULL,
    Name NVARCHAR(20) NOT NULL,
    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

    ReplyDelete
  12. Your explanations are the best Venkat!

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

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

    ReplyDelete
  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

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

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

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

    ReplyDelete
  19. anyone who have collated notes bu Kudvenkat?

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

    ReplyDelete

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