How SQL Server physically stores table data ? Clustered Indexes Internal Working.

Hey There !

In today’s blog we will be discussing below topics in brief.

1. Data Pages
2. Clustered Indexes

Here my intention is just to make you understand on higher level, how data gets stored internally in SQL Server.

We all have deal with SQL queries in our day to day job. When we say SQL, first thing come in our mind is tables.

Tabular Data View

Table is stored in rows and column wise at logical level. So what you see in above image is basically a logical view of the table. but have you ever think of how SQL server physically stores table data internally ?

Data Page

So physically data is stored into data pages. Data page is fundamental unit of data storage in SQL server and it is of 8 KB in size. Data page stores the data rows in it.

So whenever we insert data into SQL server database table it stores that data rows into series of data pages.

Consider in above example where DepartmentID is primary key. When we say primary key it means DepartmentID column will have unique values in it. So by default clustered index on this DepartmentID column gets created.

But suppose if we want to create a clustered index by yourself then below is the query,

CREATE UNIQUE CLUSTERED INDEX IX_001
ON HumanResources.Department (DepartmentID);

There can be only one clustered index per table, because the data rows themselves can be stored in only one order.

So now that we created a clustered index lets try to find out how search operation gets performed in clustered index.

let’s say we have a table with 9 rows.

Here i am assuming that each data row is stored in each data page.

Clustered indexes sort and store the data rows in the table or view based on their key values. These are the columns included in the index definition.

The root and intermediate levels contain the index key values and page pointers. The page pointers point to the previous and subsequent index pages of their own. These two levels don’t store any row data.
At the same time, index pages hold information about the ahead and behind index page numbers.

So let’s say we have below query;

select * from HumanResources.Department
where DepartmentID = 8;

The searching mechanism begins its travel at the root level and reaches the data row till last level of intermediate level. So in our case rather checking for all the data pages one by one we get final data page in only 2 levels of searches.

The uniqueness of the clustered index is very important because this constraint guarantees that only one row will return from the query.
This data searching concept is called singleton seek.

That’s it guys. Hope you like this blog.

Thank you.