Database Reference
In-Depth Information
Chapter 2
Tables and Indexes: Internal Structure
and Access Methods
SQL Server stores data in tables and indexes. They represent a collection of data pages with rows that belong to a
single entity.
By default, the data in the tables is unsorted. You can store it in sorted order by defining the clustered index on
the table. Moreover, you can create nonclustered indexes that persist another copy of the data from the index columns
sorted in a different order.
In this chapter, we will talk about the internal structure of the indexes, cover how SQL Server uses them, and
discuss how to write queries in a way that efficiently utilizes them.
Heap Tables
Heap tables
are tables without a clustered index. The data in heap tables is unsorted. SQL Server does not guarantee,
nor does it maintain, a sorting order of the data in the heap tables.
When you insert data into heap tables, SQL Server tries to fill pages as much as possible, although it does not
analyze the actual free space available on a page. It uses the Page Free Space (PFS) allocation map instead. SQL Server
errs on the side of caution, and it uses the low value from the PFS free space percentage tier during the estimation.
For example, if a data page stores 4,100 bytes of data, and as result it has 3,960 bytes of free space available, PFS
would indicate that the page is 51-80 percent full. SQL Server would not put a new row to the page if its size exceeds
20 percent (8,060 bytes * 0.2 = 1,612 bytes) of the page size. Let's look at that behavior and create the table with the
code shown in Listing 2-1.
Listing 2-1.
Inserting data into heap tables: Creating the table
create table dbo.Heap
(
Val varchar(8000) not null
);
;with CTE(ID,Val)
as
(
select 1, replicate('0',4089)
union all
select ID + 1, Val from CTE where ID < 20
)
