Firstly, we will create a database & insert some dummy records into it. Then we will see, what are Indexes & how they work.

  1. CREATE Table Library (
  2. Book_Id int identity (1, 1),
  3. Book_Name varchar(100),
  4. Dept varchar(100),
  5. Subject_Id int
  6. );
  7. DECLARE @Dept varchar(20) = 'Computer_Science' DECLARE @Book_Id int = 1 DECLARE @Count int = 1 WHILE @Count >= 1
  8. and @Count <= 500000 BEGIN INSERT INTO Library(Book_Name, Dept, Subject_Id)
  9. VALUES
  10. (
  11. 'CsharpCorner_Book' + convert(
  12. varchar(100),
  13. @Book_Id
  14. ),
  15. @Dept,
  16. @Book_Id
  17. )
  18. SET
  19. @Book_Id += 1
  20. SET
  21. @Count += 1 END
Now select first two records & see the actual execution plan (you can see execution plan after running the query from menu option of SQL Server as Query => Display Estimated Execution Plan).
  1. select * from library where book_id = 1
Query
  1. select * from library where book_id = 2
Select query

Here we can see the total cost is like the following:

Estimated Cost = Estimated I/O cost + Estimated CPU Cost

Now I am going to apply Index on my Book_Name column as:
  1. CREATE INDEX Index_BookName
  2. ON Library (Book_Name)
Now if we are looking for searching a record 'CsharpCorner_Book490000' from column name Book_Name then
select * from library where Book_Name = 'CsharpCorner_Book490000'

Now see the actual execution plan for above query.

Execution process

Indexes

Physical operator on indexes

Physical operator is an object or routine that performs an operation like Index Delete, Index Insert, Index Scan, Index Seek etc.

For more information about different indexes click here.

Real Life Example

Everyone had gone to school or college. In library if a librarian places all the books in a single shelf. Let say if any student asks for any book then it would be difficult to find because the librarian needs to search that book in the complete shelf.

Liberary

Now if librarian sort these books in alphabetical order, then it would be easier to find particular book from the all the shelves.

Book liberary

Summary

This article will help fresher candidates to understand Indexes in SQL Server with real life example.