Chapter 1 : SQL Indexes in deeper
Know more about SQL indexes
While retrieving data from SQL Server the indexes performs crucial role. The indexes helps to retrieve data faster in sql.An Index can applied on Table or Views. A Table can have more than one index.
In this article we will not go through creating indexes(we have plenty articles) rather we will focus on how they work and how can we improve much better.
In SQL server there are different type of indexes like Clustered indexes, Non-Clustered indexes, Unique indexes etc..In this article we will discuss about Clustered indexes.
Note : The data in table will be stored in Heap/Binary tree structure.
Can we create a table with out indexes ?
YES, if we don’t have any clustered index on table. The table stores the data in heap strucure(Heap table).
Heap Table :
A table without clustered index is called heap table. It doesn’t contain any logical order. The data will be inserted in data rows in a insertion order refer to following images.
Create table Customers(id int not null,Name varchar(max),email varchar(max))
insert into Customers values(1,'Raghu','r@r.com')
insert into Customers values(3,'Test3','r@r.com')
insert into Customers values(4,'Test4','r@r.com')
insert into Customers values(8,'Test8','r@r.com')
insert into Customers values(5,'Test5','r@r.com')
insert into Customers values(6,'Test6','r@r.com')
insert into Customers values(2,'Test2','r@r.com')
insert into Customers values(10,'Test10','r@r.com')
insert into Customers values(7,'Test10','r@r.com')
insert into Customers values(11,'Test11','r@r.com')
select * from customers
When a query runs to retrieve a records from a heap, SQL Server will do scanning through all data pages until it finds the records, resulting in poor performance due to the full table scan(reads all the data — 498 records).
IAM page will mitigate this some extend. Check page types created by running
“DBCC IND(‘databasename’,’Customers’,-1)”
In above diagram all the page types are created with data pages (page type as 1). To learn more about page type refer this article.
When we create a cluster index the data will reorganize the data based index column. For this reason table can’t have multiple clustered indexes.
Create clustered index and check the output.
The data is reordered based on Primary key also added index pages in page structure.
The query execution plan changed from Table Scan to Index Seek(reads single row data — 1 record).
In next article we will see how clustered indexes are stored in DB