Hey guys, I am Venkat and in this video, we'll discuss what is a heap table? How to create and
when to use it? Along the way, we'll understand the concept of table scan as well. We'll also look
at a simple example in action where a full table scan is much better from performance standpoint
than using a table index. Finally, we'll also discuss, how to force the database query engine
to use a specific index to find the data we are looking for. Now, understanding all these
concepts is very important when you're tuning sql queries for better performance. There's a quite a
bit of ground to cover, so let's get started. Consider this Employees table. EmployeeId
is the primary key, so by default, a clustered index on this column is created. This means,
the data rows that are physically stored in this Employees table are sorted by EmployeeId column.
Now, before we understand what is a heap table, let's first understand, what happens if we don't
have a clustered index on a table? Well, it is the clustered index that determines the order
in which data rows are physically stored in a table. Without a clustered index, the data rows
are not guaranteed to be in any specific order in the table. This kind of a table is called a heap.
It may have one or more non-clustered indexes but if it doesn't have a clustered index then
such a table is called a heap table or just heap. So, in short, a table without a clustered index
is called heap. Now, what do you think is going to happen when we select data from a heap? Will
the database engine use index seek, index scan or table scan? Well, it depends really, we already
know, a heap is a table without a clustered index but it may have one or more non-clustered indexes,
but for this example, let's assume, the heap does not have any non-clustered indexes as well.
If there are no non-clustered indexes on the heap, the search query uses table scan to find the data
that we are looking for. What is a table scan? Well, as the name implies, a table scan scans
every row in the table to find the data that we are looking for. Let's look at this in action.
We have a very simple sql script here. The first part of the script creates the Gender table with
two columns - GenderId and GenderName, and the second part of the script inserts three rows
into the Gender table. So, let's quickly execute the script. Now, here's the important bit to keep
in mind, notice the create table script, we did not mark any of the columns as a primary
key and we also did not create a clustered index on this table explicitly. So, the physical order
in which data rows are stored in this table is not guaranteed. In a nutshell, this Gender table is a
heap. We also did not create any non-clustered indexes. Now, a quick tip here. If you want
to check, if a given table has got any indexes on it, we can use this system stored procedure
"sp_helpindex" followed by the name of the table. So, let's execute this. There we go - The object
Gender does not have any indexes or you do not have permissions. In our case, we know the Gender
table does not have any indexes. Now, let me execute this SELECT query - Select * from Gender.
Notice the physical order in which the rows are returned by our select query. Had we marked this
GenderId column as the primary key, by default a clustered index on this column would have
been created. This means, the rows in this table would have been stored in the order of GenderId
column. Since we did not mark any of the columns in this table as primary key and we also did not
explicitly create a clustered index, the rows are by default stored in the order in which we have
inserted them. If we take a look at the GenderId values in our three insert queries, notice I have
deliberately inserted them in this order - 1, 3, 2 and that's exactly the order in which the rows
are physically stored in the database table. Now, here's the key point to keep in mind, the physical
order of rows in a heap is not always guaranteed to be in the order in which we have inserted them
and this is because, the database engine can move data around in the heap any time to store
the data rows efficiently. So, the point is, the order of data rows in a heap cannot be predicted.
To guarantee the order of rows returned from a heap, you must always use the ORDER BY clause
in your query. Now, let's execute this SELECT query. Notice, we are looking up a data row
in the Gender table by GenderName column. Keep in mind, at the moment on this Gender
table we don't have any indexes and before we execute this query, let's click on this icon to
include "Actual Execution Plan" and then execute this query. There we go, we have the one row
we expected and if we take a look at the execution plan, notice sql server is using "Table Scan"
to find the data we are looking for and when I hover the mouse over this table scan operation,
notice these two values "Number of rows read" and "Actual number of rows for all executions",
they are 3 and 1 respectively, meaning to find the one row we are looking for, sql server is
scanning all the three rows in the table. Hence, the operation name is table scan. Now, let me
hover the mouse over this SELECT operation and pay attention to "Estimated Subtree Cost" value. It is
0.0032853 which is not bad at all from performance standpoint. So, there are three key points here to
keep in mind. A table scan is used if there are no indexes to help a query that selects
data from a table heap. Table scan is not always bad from performance standpoint especially if you
have a table with a very few rows like this Gender table. Even if there is an index on a very small
table like this Gender table, sql server might still end up using table scan. This is because a
table scan provides better performance than using the index. Let's actually prove this last point.
Take a look at this sql code here. We are creating a non-clustered index on the GenderName column.
Why on the GenderName column? Well, that's because we are looking up data by GenderName column. So,
it makes sense to create a non-clustered index on this column. So, let's execute this query.
Index created. So, let's execute our SELECT query again. If we take a look at the execution plan,
notice though we have a non-clustered index on GenderName column, sql server is still using table
scan instead of index seek. This is because, the database engine knows table scan produces better
performance than index seek and that's because there are very few rows in the Gender table.
So, another key point to keep in mind here is, if the table has very few rows and even if there is
an index to help the query, the database engine might still end up performing a full table scan
instead of using the index. Now, can we prove for a very small table like this Gender table,
a table scan is better than index seek? Well, we can but we have to force the query optimizer
to use the non-clustered index that we have on the GenderName column and for that, we use this "with"
keyword. So, basically this is the same SELECT query, but we are using this "with" keyword here
to tell the query optimizer to use this non-clustered index that we have. So,
let's execute this query. If we take a look at the execution plan, notice sql server
is now using index seek operation and when I hover the mouse over this operation, notice the values
for "Number of rows read" and "Actual number of rows for all executions", it's the same - 1.
So, with the help of the index, sql server is able to find that one record we want. Now,
let's find out the overall "Estimated subtree cost" for all these operations and for that,
we hover the mouse over this SELECT operation. Notice, the value it is 0.0065704.
I have the values side by side here. Notice, the overall "Estimated subtree cost"
of table scan is lower than index seek. So, the bottom line is, we have better performance with
table scan than using the index. Hence, the query optimizer chose table scan over using the index.
How to create and remove heaps? Well, to create a heap, create a table without a clustered index.
If a table already has a clustered index then drop the clustered index to return the table to
a heap. If you already have a heap and you want to remove it, simply create a clustered index
on that heap. When to use a heap? Well, a heap can be used as a staging table for large and
unordered insert operations because data is inserted without enforcing a strict order,
the insert operation is usually faster than the equivalent insert into a table with clustered
index. We can also use a heap when the table is small with just a few rows like our Gender table,
or we can also use heap, when we know data is always accessed through non-clustered indexes.
Now, when we executed this query that's making use of the index we have, notice, in the
execution plan along with index seek operation, we also have RID lookup. RID stands for "Row Id".
Another common operation that we see in execution plans is - Key Lookup. Understanding both these
operations is very important especially when we are tuning sql queries for performance. So,
in our next video, we'll understand what these two operations are and the differences between them
with simple examples. That's it in this video. Thank you for listening.