Hey guys, I am Venkat and in this video, we'll  understand how sql server stores data internally.  
Hey guys, I am Venkat and in this video, we'll  understand how sql server stores data internally.  

As a software engineer this knowledge is very  important, especially if you want to troubleshoot  
As a software engineer this knowledge is very  important, especially if you want to troubleshoot  

and fix sql queries that are not performing very  well from performance standpoint. Along the way,  
and fix sql queries that are not performing very  well from performance standpoint. Along the way,  

we'll also understand some of the common  technical terms like the following - Data pages,  
we'll also understand some of the common  technical terms like the following - Data pages,  

root node, leaf nodes, b tree and clustered index  structure. Understanding these terms is very  
root node, leaf nodes, b tree and clustered index  structure. Understanding these terms is very  

important, especially if you're doing something  related to sql server performance tuning. Now,  
important, especially if you're doing something  related to sql server performance tuning. Now,  

have you ever wondered how sql server physically  stores table data internally? Well, data in tables  
have you ever wondered how sql server physically  stores table data internally? Well, data in tables  

is stored in row and column format at the logical  level but physically it stores data in something  
is stored in row and column format at the logical  level but physically it stores data in something  

called data pages. A data page is the fundamental  unit of data storage in sql server and it is eight  
called data pages. A data page is the fundamental  unit of data storage in sql server and it is eight  

kilobytes in size. When we insert any data into  sql server database table, it saves that data to  
kilobytes in size. When we insert any data into  sql server database table, it saves that data to  

a series of 8 kilobytes data pages. So, table data  in sql server is actually stored in a tree-like  
a series of 8 kilobytes data pages. So, table data  in sql server is actually stored in a tree-like  

structure. Let's understand this with a simple  example. Consider this Employees table. In this  
structure. Let's understand this with a simple  example. Consider this Employees table. In this  

table EmployeeId is the primary key column.  So, by default, a clustered index on this  
table EmployeeId is the primary key column.  So, by default, a clustered index on this  

EmployeeId column is created. This means, the data  is physically stored in the database is sorted by  
EmployeeId column is created. This means, the data  is physically stored in the database is sorted by  

EmployeeId column. Now, where is the data actually  stored? Well, it is stored in a series of data  
EmployeeId column. Now, where is the data actually  stored? Well, it is stored in a series of data  

pages in a tree-like structure that looks like  the following. This tree-like structure is called  
pages in a tree-like structure that looks like  the following. This tree-like structure is called  

B-tree, Index B-Tree or clustered index structure,  all these three terms mean the same thing.  
B-tree, Index B-Tree or clustered index structure,  all these three terms mean the same thing.  

The nodes that you see at the bottom of the tree  are called data pages or leaf nodes of the tree  
The nodes that you see at the bottom of the tree  are called data pages or leaf nodes of the tree  

and it is these leaf nodes that contain our table  data. The size of each data page is 8 kilobytes,  
and it is these leaf nodes that contain our table  data. The size of each data page is 8 kilobytes,  

this means the number of rows that are stored in  each data page really depends on the size of each  
this means the number of rows that are stored in  each data page really depends on the size of each  

row. For our example, let's say in this Employees  table we have 1200 rows and let's assume in  
row. For our example, let's say in this Employees  table we have 1200 rows and let's assume in  

each data page we have 200 rows but in reality  depending on the actual row size, we may have more  
each data page we have 200 rows but in reality  depending on the actual row size, we may have more  

or less rows, but for this example sake, let's  assume each data page has 200 rows. Remember, the  
or less rows, but for this example sake, let's  assume each data page has 200 rows. Remember, the  

important point to keep in mind is, the rows in  these data pages are sorted by EmployeeId column,  
important point to keep in mind is, the rows in  these data pages are sorted by EmployeeId column,  

because EmployeeId is the primary key of our table  and hence the clustered key. So, in the first data  
because EmployeeId is the primary key of our table  and hence the clustered key. So, in the first data  

page, we have 1 to 200 rows, in the second 201 to  400, in the third 401 to 600 so on and so forth.  
page, we have 1 to 200 rows, in the second 201 to  400, in the third 401 to 600 so on and so forth.  

The node at the top of the tree is called root  node. The nodes between the root node and the leaf  
The node at the top of the tree is called root  node. The nodes between the root node and the leaf  

nodes are called intermediate levels. There can  be multiple intermediate levels. In our example,  
nodes are called intermediate levels. There can  be multiple intermediate levels. In our example,  

we have just 1200 rows and to keep this example  simple, I included just one intermediate level,  
we have just 1200 rows and to keep this example  simple, I included just one intermediate level,  

but in reality, the number of intermediate  levels depends on the number of rows you  
but in reality, the number of intermediate  levels depends on the number of rows you  

have in the underlying database table. The  root and the intermediate level nodes contain  
have in the underlying database table. The  root and the intermediate level nodes contain  

index rows and the leaf nodes, that is nodes  at the bottom of the tree, contain the actual  
index rows and the leaf nodes, that is nodes  at the bottom of the tree, contain the actual  

data rows. Each index row contains a key  value, in our case EmployeeId and a pointer  
data rows. Each index row contains a key  value, in our case EmployeeId and a pointer  

to either an intermediate level page in the  B-tree or a data row in the leaf node. So, the  
to either an intermediate level page in the  B-tree or a data row in the leaf node. So, the  

important point is, this tree-like structure has a  series of pointers that helps the database engine  
important point is, this tree-like structure has a  series of pointers that helps the database engine  

find the data quickly. For example, let's say we  want to find employee row with EmployeeId = 1120.  
find the data quickly. For example, let's say we  want to find employee row with EmployeeId = 1120.  

So, the query itself is very simple - Select  * from employees where employee id = 1120. So,  
So, the query itself is very simple - Select  * from employees where employee id = 1120. So,  

the database engine starts at the root node and  from there it picks the index node on the right  
the database engine starts at the root node and  from there it picks the index node on the right  

because the database engine knows it is this node  that contains EmployeeIds from 801 to 1200. From  
because the database engine knows it is this node  that contains EmployeeIds from 801 to 1200. From  

there, it picks the leaf node that is present  on the extreme right because employee data rows  
there, it picks the leaf node that is present  on the extreme right because employee data rows  

from 1001 to 1200 are present in this leaf  node. The data rows in the leaf node are sorted  
from 1001 to 1200 are present in this leaf  node. The data rows in the leaf node are sorted  

by EmployeeId, so it's easy for the database  engine to find the employee row with Id = 1120.  
by EmployeeId, so it's easy for the database  engine to find the employee row with Id = 1120.  

Notice, in just three operations sql server is  able to find the data we are looking for. So, the  
Notice, in just three operations sql server is  able to find the data we are looking for. So, the  

point is, if there are thousands or even millions  of records, sql server can easily and quickly  
point is, if there are thousands or even millions  of records, sql server can easily and quickly  

find the data we are looking for provided there  is an index that can help the query find data.  
find the data we are looking for provided there  is an index that can help the query find data.  

In this specific example, there is a clustered  index on EmployeeId column. So, when we search  
In this specific example, there is a clustered  index on EmployeeId column. So, when we search  

by EmployeeId, sql server can easily and  quickly find the data we are looking for,  
by EmployeeId, sql server can easily and  quickly find the data we are looking for,  

but what if we search by employee name? At the  moment, there is no index on the Name column,  
but what if we search by employee name? At the  moment, there is no index on the Name column,  

so there is no easy way for sql server to  find the data we are looking for. SQL server  
so there is no easy way for sql server to  find the data we are looking for. SQL server  

has to read every record in the table which is  extremely inefficient from performance standpoint.  
has to read every record in the table which is  extremely inefficient from performance standpoint.  

This is when we create a non-clustered index on  the Name column. In our next video in the series,  
This is when we create a non-clustered index on  the Name column. In our next video in the series,  

we'll discuss, how the index and table  data is physically stored when we have both  
we'll discuss, how the index and table  data is physically stored when we have both  

a non-clustered index and a clustered index.  We'll also discuss - Index Seek and Index Scan.  
a non-clustered index and a clustered index.  We'll also discuss - Index Seek and Index Scan.  

Understanding these two concepts is very important  especially when we are tuning sql queries  
Understanding these two concepts is very important  especially when we are tuning sql queries  

for better performance. We'll see all these  practically in action in our next video, so please  
for better performance. We'll see all these  practically in action in our next video, so please  

stay tuned. Before we wrap up, if you're new to  sql server indexes we have already discussed them  
stay tuned. Before we wrap up, if you're new to  sql server indexes we have already discussed them  

in detail in our sql server tutorial for beginners  course. Please check out the videos from Parts 35  
in detail in our sql server tutorial for beginners  course. Please check out the videos from Parts 35  

to 38. I'll include the link to this  course in the description of this video.  
to 38. I'll include the link to this  course in the description of this video.  

I hope you found this video useful. That's  it in this video and thank you for listening.
I hope you found this video useful. That's  it in this video and thank you for listening.