Database Reference
In-Depth Information
INSERT INTO dbo.Test1
VALUES (100, 'C2', ''),
(200, 'C2', ''),
(300, 'C2', ''),
(400, 'C2', ''),
(500, 'C2', ''),
(600, 'C2', ''),
(700, 'C2', ''),
(800, 'C2', '');
CREATE CLUSTERED INDEX iClust
ON dbo.Test1(C1);
The average size of a row in the clustered index leaf page (excluding internal overhead) is not just the sum of the
average size of the clustered index columns; it's the sum of the average size of all the columns in the table, since the
leaf page of the clustered index and the data page of the table are the same. Therefore, the average size of a row in the
clustered index based on the foregoing sample data is as follows:
= (Average size of [C1]) + (Average size of [C2]) + (Average size of [C3]) bytes = (Size of INT) +
(Size of CHAR(999)) + (Average size of data in [C3]) bytes
= 4 + 999 + 0 = 1,003 bytes
The maximum size of a row in SQL Server is 8,060 bytes. Therefore, if the internal overhead is not very high, all
eight rows can be accommodated in a single 8KB page.
To determine the number of leaf pages assigned to the
iClust
clustered index, execute the
SELECT
statement
against
sys.dm_db_index_physical_stats
.
SELECT ddips.avg_fragmentation_in_percent,
ddips.fragment_count,
ddips.page_count,
ddips.avg_page_space_used_in_percent,
ddips.record_count,
ddips.avg_record_size_in_bytes
FROM sys.dm_db_index_physical_stats(DB_ID('AdventureWorks2012'),
OBJECT_ID(N'dbo.Test1'), NULL,
NULL,'Sampled') AS ddips ;
You can see the results of this query in Figure
13-4
.
Figure 13-4.
Physical layout of index iClust
From the
page_count
column in this output, you can see that the number of pages assigned to the clustered
index is 1. You can also see the average space used, 100, in the
avg_ page_space_used_in_percent
column. From
this you can infer that the page has no free space left to expand the content of
C3
, which is of type
VARCHAR(10)
and is
currently empty.
