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
