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

serge (sergea (AT) nospam (DOT) ehmail.com) writes:
No. You are forgetting that there is fixed overhead of each row of at
least nine bytes. See page 253 in "Inside SQL Server 2000".

454*17 is still only 7718, but I believe that there is some margin
left for the page to grow.

Actually it's called a "uniquifier". I like to make this distinction,
since uniqueidentifier is a data type which is 16 bytes wide.


--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx