Posted by serge on 03/02/07 19:46

IF (SELECT OBJECT_ID('t1')) IS NOT NULL
DROP TABLE t1
GO

CREATE TABLE t1 (c1 INT, c2 INT)
DECLARE @n INT
SET @n = 1
WHILE @n <= 454
BEGIN
INSERT INTO t1 VALUES (@n, @n)
SET @n = @n + 1
END

SELECT name, indid, CASE indid
WHEN 0 THEN 'Table'
WHEN 1 THEN 'Clustered Index'
ELSE 'Nonclustered Index'
END AS Type,
dpages, rowcnt
FROM sysindexes
WHERE id = OBJECT_ID('T1')


name indid Type dpages rowcnt
---- ----- ---- ------ ------
NULL 0 Table 2 454


I have a table containing 454 rows of two columns
of type INT with each being 4 bytes

c1 int = 4 bytes
+
c2 int = 4 bytes
=
8 bytes per row

If I entered 454 rows : 454 * 8 = 3,632 bytes

each SQL Page is 8KB = 8 * 1024 bytes
= 8,192 bytes

a data page header takes the first 96 bytes
leaving 8096 bytes for data and row offsets.

Each record uses a row offset at the end of the page
consisting of 2 bytes. 454 * 2 = 908 bytes.

8096 - 3632 - 908 = 3,556 bytes. Should this be
free data bytes?

For a heap table, does SQL add an internal uniqueidentifier
column also? or my question is when does SQL add
a uniqueidentifier? I am reading Inside SQL 2000 and
trying to understand a few things.
A uniqueidentifier of 4 bytes gets added when a clustered index
exists but it is NOT a UNIQUE clustered index. AND only
if duplicate record is added those two records only get
a uniqueidentifier value.

But in my example it's a heap table with no indexes. Even
on a heap table with no indexes a ROWID or Uniqueidentifier
get added? Based on the INSERT statement above all
values are unique.

So what am I missing to understand why 453 rows
make one data page to be used whereas 454 rows
make two data pages to be used?


Thank you

• Next in forum: Re: Recursive CTE Question
• Prev in forum: Recursive CTE Question
• Thread view: Heap table: why 454 rows of two INT columns use 2 data pages?

[Reply to this message]