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
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,
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,
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
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
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
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. 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
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
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
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
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,
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.
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,
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
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
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
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.
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
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
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.
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
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
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,
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.
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
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
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
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.
I hope you found this video useful. That's it in this video and thank you for listening.