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.
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
excellent demonstration
ReplyDeleteYour instructions are so clear and easy to understand.Thanks.
ReplyDeleteexcellent 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..
ReplyDeleteYour tutorials are so easy to understand. Thanks so much for all your hard work. I loved your text explanation and all the video tutorials.
ReplyDeleteTo 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!
Thanks very much... for clear & patient explanation
DeleteI am very grateful for all the knowledge learned, thank you very much Pragim.
ReplyDeleteTo view the Indexes:
ReplyDeleteExecute sp_helpindex tblEmployee
sp_help 'EmployeeDetails' its not sp_helptext
ReplyDeleteNagendra , Thanks for rectifying the mistake .
Deleteits sp_helpindex.
DeleteSp_help shows all information but prior shown only about indexes.
Nice tutorial Venkat,very well understood indexes in SQL,Thanks
ReplyDeleteGreat Explanation ,i want to say thank you from the bottom of my heart
ReplyDeleteNice Tutorials. Thanks.
ReplyDeleteI think it Execute sp_helpindex tblEmployee not Execute sp_helptext tblEmployee
ReplyDeleteBelow, please find the SQL Query for creating the tblEmployee table.
ReplyDeleteCREATE 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
Your explanations are the best Venkat!
ReplyDeleteExecute sp_helptext tblEmployee
ReplyDeleteyou mentioned this one not working instead of these youcan use
Execute sp_helpIndex tblEmployee
whether there is any drawback in creating the index on PK
ReplyDeleteHi Venkat, Very beneficial tutorials provided by you. I appreciate your effort & thank you so much for all your hard work. God bless you
ReplyDeletePlease edit this..
ReplyDeleteExecute sp_helptext tblEmployee to Execute sp_helpindex tblEmployee.
you're the best teacher sir....
ReplyDeleteyours all session are best of best...
To view index its
ReplyDeleteExecute sp_helpindex tblename
Its not sp_helptext
anyone who have collated notes bu Kudvenkat?
ReplyDeleteHi venkat
ReplyDeleteIf I need to order salary column as desc or asc how can I do it using graphically