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 uniqueidentifie r
column also? or my question is when does SQL add
a uniqueidentifie r? I am reading Inside SQL 2000 and
trying to understand a few things.
A uniqueidentifie r 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 uniqueidentifie r value.
But in my example it's a heap table with no indexes. Even
on a heap table with no indexes a ROWID or Uniqueidentifie r
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
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 uniqueidentifie r
column also? or my question is when does SQL add
a uniqueidentifie r? I am reading Inside SQL 2000 and
trying to understand a few things.
A uniqueidentifie r 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 uniqueidentifie r value.
But in my example it's a heap table with no indexes. Even
on a heap table with no indexes a ROWID or Uniqueidentifie r
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
Comment