Clustered Indexes, Non Clustered Indexes & why?

Creating Indexes on tables reduces the query retrieval time and increase the efficiency of SQL queries or statements fired against a database in SQL Server. Indexes are just like a Table of Contents in front side of the book or Index section at the back side of the book.

There are mainly 2 types of Indexes, CLUSTERED & NON-CLUSTERED index which can be created on a table.
- Clustered indexes are similar to a telephone directory where you search a person’s name alphabetically and get his phone number there only.
- Non Clustered indexes are similar to the Index of a book where you get the page number of the item you were searching for. Then turn to that page and read what you were looking for.

According to MS BOL one can create only one Clustered index & as many 249 Non Clustered indexes on a single table.

But why there is a need to create these indexes, what causes the fast retrival of data from the tables.
Let’s check this by creating a large table and creating these Indexes one by one and checking as we go one:

USE [AdventureWorks]

GO

select * from Sales.SalesOrderDetail -- Total 121317 records

select * from Production.Product -- Total 504 records

SELECT s.SalesOrderDetailID, s.SalesOrderID, s.ProductID, p.Name as ProductName, s.ModifiedDate

INTO IndexTestTable

FROM Sales.SalesOrderDetail s

JOIN Production.Product p

on p.ProductID = s.ProductID

GO

-- Test the table without any Indexes which is also a HEAP

SELECT TOP 10 * FROM IndexTestTable

--////////////////////////////////////////////////

--// Scenario 1 : When there is no Clustered Index

--////////////////////////////////////////////////

SET STATISTICS PROFILE ON

SET STATISTICS TIME ON

SELECT SalesOrderDetailID FROM IndexTestTable WHERE SalesOrderDetailID = 60000

SET STATISTICS TIME OFF

SET STATISTICS PROFILE OFF

GO