Note : In order to try this example you need to take a Dedicated
Administrator Connection (check BOL http://msdn.microsoft.com/en-us/library/ms189595.aspx). Try
this example only on your dev box.
-- Lets create a database as sysobject
CREATE DATABASE sysobject;
USE sysobject
GO
SELECT COUNT(1) cnt FROM sys.sysschobjs;-- Each row in this table represents and object
SELECT COUNT(1) cnt FROM sys.syscolpars;-- Contains a row for every column in a table
SELECT COUNT(1) cnt FROM sys.sysidxstats;-- Contains a row for each index/statistics for table
SELECT COUNT(1) cnt FROM sys.sysallocunits;-- Contains a row for each allocation unit
-- I create a table now and see the changes in the base tables by firing the select statement above
CREATE TABLE Test(c1 int NOT NULL, c2 varchar(10));
As can be seen clearly no of objects increased by 1 to 54, no of columns increased by 2 to 485. Statistics for the table increased the count by 1 in sysidxstats to 182. The allocation unit count goes up by 1 to 104, since the table has added 1 IN_ROW_DATA allocation unit
-- Lets now create an index on this table and see the changes in sysidxstats
CREATE INDEX idx_Test ON Test(c2);
Execute the count again. This time the count for sysidxstats goes up by 1 to 183. This is for the index that we created, also the the count of sysallocunits goes up by 1 to 105 due to the index allocation unit
Lets now add a primary key on this table. Different thing to note here is that the only thing that changed is the object count. It has now increased by 1 to 55.
ALTER TABLE Test ADD CONSTRAINT pk_Test PRIMARY KEY (c1);
Lets add a check constraint now and see. Clearly we can see that check constraint also has gone into the sysschobjs.
ALTER TABLE Test ADD CONSTRAINT ck_Test CHECK (len(c2)<= 6)
Finally add a default constraint now and see. And we see that default constraint also has gone into the sysschobjs.
ALTER TABLE Test ADD CONSTRAINT df_c1 DEFAULT 10 FOR c1;
Get the objects id for the system table sysschobjs. We will use this information to find out the pages that are allocated to this system table
SELECT * FROM sys.objects where name ='sysschobjs'
Based on the object_id received (34) we check the pages allocated to this system table. As can be seen there are 2 pages. We will read the data page 116 and leave the IAM page 117
DBCC IND(sysobject,34,1)
I will only show the extract from this page as it would take too much space to show all the information
DBCC TRACEON(3604)
DBCC PAGE(sysobject,1,116,1)
Record Type= PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
Record Size = 60
Memory Dump @0x000000000838B326
0000000000000000: 30002c00 e7a4787d 01000000 0000000e 0.,.x}........
0000000000000010: 00552000 00000001 02000000 5e6bfe00 .U .........^k.
0000000000000020: 3d9d0000 dc630101 3d9d0000 0b000000 =...c..=.......
0000000000000030: 01003c00 54006500 73007400 ..<.T.e.s.t.
Slot 55, Offset 0x1362,Length 66, DumpStyle BYTE
Record Type= PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
Record Size = 66
Memory Dump @0x000000000838B362
0000000000000000: 30002c00 20c96c7e 01000000 00000000 0.,. l~........
0000000000000010: 00504be7 a4787d01 00000000 6078ff00 .PKx}.....`x.
0000000000000020: 3d9d0000 6078ff00 3d9d0000 0b000000 =...`x.=.......
0000000000000030: 01004200 70006b00 5f005400 65007300 ..B.p.k._.T.e.s.
0000000000000040: 7400t.
Slot 56, Offset 0x13a4,Length 66, DumpStyle BYTE
Record Type= PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
Record Size = 66
Memory Dump @0x000000000838B3A4
0000000000000000: 30002c00 59ed607f 01000000 00000012 0.,.Y`.........
0000000000000010: 004320e7 a4787d01 02000000 33810001 .C x}.....3...
0000000000000020: 3d9d0000 33810001 3d9d0000 0b000000 =...3...=.......
0000000000000030: 01004200 63006b00 5f005400 65007300 ..B.c.k._.T.e.s.
0000000000000040: 7400t.
OFFSET TABLE:
Row - Offset
56(0x38)- 5028(0x13a4)
55(0x37)- 4962(0x1362)
54(0x36)- 4902(0x1326)
As can be seen clearly above that
This also makes the reason why in a database cannot have 2 primary keys with the same name :-). I have taken only a few system tables, however there are other system tables that will have changes as wwell. I leave that to you to investigate.
-- Lets create a database as sysobject
CREATE DATABASE sysobject;
USE sysobject
GO
SELECT COUNT(1) cnt FROM sys.sysschobjs;-- Each row in this table represents and object
SELECT COUNT(1) cnt FROM sys.syscolpars;-- Contains a row for every column in a table
SELECT COUNT(1) cnt FROM sys.sysidxstats;-- Contains a row for each index/statistics for table
SELECT COUNT(1) cnt FROM sys.sysallocunits;-- Contains a row for each allocation unit
-- I create a table now and see the changes in the base tables by firing the select statement above
CREATE TABLE Test(c1 int NOT NULL, c2 varchar(10));
As can be seen clearly no of objects increased by 1 to 54, no of columns increased by 2 to 485. Statistics for the table increased the count by 1 in sysidxstats to 182. The allocation unit count goes up by 1 to 104, since the table has added 1 IN_ROW_DATA allocation unit
-- Lets now create an index on this table and see the changes in sysidxstats
CREATE INDEX idx_Test ON Test(c2);
Execute the count again. This time the count for sysidxstats goes up by 1 to 183. This is for the index that we created, also the the count of sysallocunits goes up by 1 to 105 due to the index allocation unit
Lets now add a primary key on this table. Different thing to note here is that the only thing that changed is the object count. It has now increased by 1 to 55.
ALTER TABLE Test ADD CONSTRAINT pk_Test PRIMARY KEY (c1);
Lets add a check constraint now and see. Clearly we can see that check constraint also has gone into the sysschobjs.
ALTER TABLE Test ADD CONSTRAINT ck_Test CHECK (len(c2)<= 6)
Finally add a default constraint now and see. And we see that default constraint also has gone into the sysschobjs.
ALTER TABLE Test ADD CONSTRAINT df_c1 DEFAULT 10 FOR c1;
Get the objects id for the system table sysschobjs. We will use this information to find out the pages that are allocated to this system table
SELECT * FROM sys.objects where name ='sysschobjs'
Based on the object_id received (34) we check the pages allocated to this system table. As can be seen there are 2 pages. We will read the data page 116 and leave the IAM page 117
DBCC IND(sysobject,34,1)
I will only show the extract from this page as it would take too much space to show all the information
DBCC TRACEON(3604)
DBCC PAGE(sysobject,1,116,1)
Record Type= PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
Record Size = 60
Memory Dump @0x000000000838B326
0000000000000000: 30002c00 e7a4787d 01000000 0000000e 0.,.x}........
0000000000000010: 00552000 00000001 02000000 5e6bfe00 .U .........^k.
0000000000000020: 3d9d0000 dc630101 3d9d0000 0b000000 =...c..=.......
0000000000000030: 01003c00 54006500 73007400 ..<.T.e.s.t.
Slot 55, Offset 0x1362,Length 66, DumpStyle BYTE
Record Type= PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
Record Size = 66
Memory Dump @0x000000000838B362
0000000000000000: 30002c00 20c96c7e 01000000 00000000 0.,. l~........
0000000000000010: 00504be7 a4787d01 00000000 6078ff00 .PKx}.....`x.
0000000000000020: 3d9d0000 6078ff00 3d9d0000 0b000000 =...`x.=.......
0000000000000030: 01004200 70006b00 5f005400 65007300 ..B.p.k._.T.e.s.
0000000000000040: 7400t.
Slot 56, Offset 0x13a4,Length 66, DumpStyle BYTE
Record Type= PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
Record Size = 66
Memory Dump @0x000000000838B3A4
0000000000000000: 30002c00 59ed607f 01000000 00000012 0.,.Y`.........
0000000000000010: 004320e7 a4787d01 02000000 33810001 .C x}.....3...
0000000000000020: 3d9d0000 33810001 3d9d0000 0b000000 =...3...=.......
0000000000000030: 01004200 63006b00 5f005400 65007300 ..B.c.k._.T.e.s.
0000000000000040: 7400t.
OFFSET TABLE:
Row - Offset
56(0x38)- 5028(0x13a4)
55(0x37)- 4962(0x1362)
54(0x36)- 4902(0x1326)
As can be seen clearly above that
- slot 55 : is the entry for table Test
- slot 56 : is the entry for primary key pk_Test
- slot 57 : is the entry for check constraint ck_Test
This also makes the reason why in a database cannot have 2 primary keys with the same name :-). I have taken only a few system tables, however there are other system tables that will have changes as wwell. I leave that to you to investigate.
