豢豌: http://sqler.pe.kr/web_board/view_list.asp?id=7858&read=1188&pagec=4&found=is&part=myboard4&ser=yes

蠏谿...螳 覯 企蟆 襴襯  れ~




ps : page蟲譟 蠏碁殊 蟇磯襷 轟危語 殊蟆

 5 (覃伎 - 覿朱 螳  豢 )

Test蟆 : 蠍SQL-Server 2000 SP3

一危郁 ル  碁煙 伎襯 伎, 譯 伎襷 危エ覲伎.

/*---------------------------------------------------------------------
dbcc traceon(3604) : 企殊伎誤碁 蟆郁骸襯 覦

DBCC PAGE : 一危 伎 蟲譟磯ゼ 覲   覈
(覓語 讌  DBCC 覈轟 企  覈轟  ろ 覓語 MS 
 豈 讌讌 給. - 覦 ろ語朱襷 蠍 覦.)

DBCC PAGE ({dbid|dbname}, filenum, pagenum, print option)
    dbid|dbname - 伎襯 螻  db id  企
    filenum - 伎襯 螻  殊 覯
    pagenum - 朱伎 伎 覯
    print option - 豢レ旧
    0 - 覯 る 伎 る襯 豢(吴)
    1 - 覯 る, 伎 る, 螳 ,  ろ 企れ 豢
    2 - 覯 覦 伎 る, 伎 豌,  ろ 企れ 豢
    3 - 覯 る, 伎 る, 螳,  ろ 企れ 豢
---------------------------------------------------------------------*/
use tempdb  --覈 ろ碁 tempdb
go
/*------------------------------------------------------------------------------*/
-- data page リ規譟
/*------------------------------------------------------------------------------*/
1. 螻蠍語 貉朱 蟆曙
/*------------------------------------------------------------------------------*/
Status Bits(1 byte) + 讌 (1 byte) + 貉朱れ 襯 谿場 豺(2 byte) + 
1,2,...n覯讌 螻蠍語 貉殊 data + 貉朱れ (2 byte) + 
null 觜碁У(n byte : 螳貉朱 1bit)
/*------------------------------------------------------------------------------*/
--drop table a
create table a (id int identity, c1 char(10))
go
insert into a values ('a')
insert into a values (null)
go
select indid, first from sysindexes where id=object_id('a')

indid  first          
------ -------------- 
0      0x0F0000000100 => 0F000000 : 伎覯, 0100 : 朱

伎覦覯 : れ 覿 2襴 伎 れ伎 覺狩.
讀, 0x0F 00 00 00 01 00 => 00 01 00 00 00 0F 企一朱 譬磯ゼ れ 覺 ,
sysindexes 企 first貉殊 蟆曙 朱(2byte) + 伎覯(4byte)襦 蟲焔 .

select convert(int, 0x0f)
----------- 
15

dbcc traceon(3604)
dbcc page (tempdb, 1, 15, 1)

Slot 0, Offset 0x60
-------------------
Record Type = PRIMARY_RECORD                        
Record Attributes =  NULL_BITMAP                    
19AD6060:  00120010  00000001  20202061  20202020 ........a       
19AD6070:  00022020        00       

伎覦覯 :  襷谿螳讌襦 れ 覿 2襴 伎 れ伎 覺狩.
蠏碁磯,   覿 覲企蟆 襷(?)...
19AD6060:  10 00 12 00    01 00 00 00    61 20 20 20    20 20 20 20
19AD6070:  20 20 02 00    00

Status Bits(1 byte) : 10
讌 (1 byte) : 00
貉朱れ 襯 谿場 豺(2 byte) : 1200 願 襷蟾 0012 願 襷蟾 ?
id貉殊 data(4 byte) : 01000000  --> 00000001 覓企 願 襷...
c1貉殊 data(10 byte) : 61202020202020202020   --> 襷襦 10 byte螻糾谿讌
貉朱れ (2 byte) : 0200
null 觜碁У(1 byte) : 00

Slot 1, Offset 0x75
-------------------
Record Type = PRIMARY_RECORD                        
Record Attributes =  NULL_BITMAP                    
19AD6075:  00120010  00000002  00000000  00000000 ................
19AD6085:  00020000        02                     .....

Status Bits(1 byte) : 10
讌 (1 byte) : 00
貉朱れ 襯 谿場 豺(2 byte) : 1200
id貉殊 data(4 byte) : 02000000
c1貉殊 data(10 byte) : 00000000000000000000   --> null 伎襷 襷襦 10 byte螻糾谿讌
貉朱れ (2 byte) : 0200
null 觜碁У(1 byte) : 02 => 2覯讌 貉殊 null企.

蠏碁覃, syscolumns 企 offset螻 觜蟲企慨.

select convert(varchar(10),name) name, colid, xoffset from syscolumns where id=object_id('a')

name       colid  xoffset 
---------- ------ ------- 
id         1      4
c1         2      8

id貉殊 offset 4願 c1貉殊 8企.  蟆郁骸 觜蟲企慨覃 
id貉殊 5byte覿 c1貉殊 9byte覿   . 
sql覯 企一朱 螻蠍語 貉殊 谿場企 蟆企.

/*------------------------------------------------------------------------------*/
2. 螳覲蠍語 貉殊 蟆曙
/*------------------------------------------------------------------------------*/
Status Bits(1 byte) + 讌 (1 byte) + 貉朱れ 襯 谿場 豺(2 byte) + 
1,2,...n覯讌 螻蠍語 貉殊 data + 貉朱れ (2 byte) + null 觜碁У(1 byte) + 
螳覲蠍語 貉殊 (2 byte) + 1,2,...n覯讌 螳覲蠍語 貉殊  豺(2 byte) + 
1,2,...n覯讌 螳覲蠍語 貉 data
/*------------------------------------------------------------------------------*/
=> 企 語  襦 ル蟆  螻蠍語 貉朱れ 朱 り
   螳覲蠍語 貉朱れ る る 朱 ロ.
/*------------------------------------------------------------------------------*/
--drop table a
create table a (id int identity, c1 varchar(10), c2 char(10))
go
insert into a values ('a', 'a')
insert into a values (null,null)
go
select indid, first from sysindexes where id=object_id('a')

indid  first          
------ -------------- 
0      0x0F0000000100 => 0F000000 : 伎覯, 0100 : 朱

select convert(int, 0x0f)
----------- 
15

dbcc traceon(3604)
dbcc page (tempdb, 1, 15, 1)

Slot 0, Offset 0x60
-------------------
Record Type = PRIMARY_RECORD                        
Record Attributes =  NULL_BITMAP VARIABLE_COLUMNS   
19AD6060:  00120030  00000001  20202061  20202020 0.......a       
19AD6070:  00032020  1a000100      6100             .......a

Status Bits(1 byte) : 30
讌 (1 byte) : 00
貉朱れ 襯 谿場 豺(2 byte) : 1200
id貉殊 data(4 byte) : 01000000
c2貉殊 data(10 byte) : 61202020202020202020   --> 襷襦 10 byte螻糾谿讌
貉朱れ (2 byte) : 0300
null 觜碁У(1 byte) : 00
螳覲蠍語 貉殊 (2 byte) : 0100
豌覯讌 螳覲蠍語 貉殊  豺(2 byte) : 1a00
豌覯讌 螳覲蠍語 貉 data : 61

Slot 1, Offset 0x7a
-------------------
Record Type = PRIMARY_RECORD                        
Record Attributes =  NULL_BITMAP                    
19AD607A:  00120010  00000002  00000000  00000000 ................
19AD608A:  00030000        06                     .....

Status Bits(1 byte) : 10
讌 (1 byte) : 00
貉朱れ 襯 谿場 豺(2 byte) : 1200
id貉殊 data(4 byte) : 02000000
c2貉殊 data(10 byte) : 00000000000000000000   --> null 伎襷 襷襦 10 byte螻糾谿讌
貉朱れ (2 byte) : 0300
null 觜碁У(1 byte) : 06 => 2覯讌, 3覯讌 貉殊 null 企.
螳覲蠍語 貉殊 (2 byte) : 
豌覯讌 螳覲蠍語 貉殊  豺(2 byte) : 
豌覯讌 螳覲蠍語 貉 data : 

=> 螳覲蠍語 貉殊 null れ伎る 螻糾 谿讌讌 .

蠏碁覃, syscolumns 企 offset螻 觜蟲企慨.

select convert(varchar(10),name) name, colid, xoffset from syscolumns where id=object_id('a')

name       colid  xoffset 
---------- ------ ------- 
id         1      4
c1         2      -1
c2         3      8

id貉殊 offset 4願 c1貉殊 -1願 c2貉殊 8企.  蟆郁骸 觜蟲企慨覃 
id貉殊 5byte覿 c2貉殊 9byte覿 螻 c1貉殊 豌覯讌 螳覲蠍語 貉殊  . 
蠏碁, c1 蟆曙磯 豢螳朱 豌覯讌 螳覲蠍語 貉殊  豺襯 谿場伎伎
蠏瑚崖 data襯 蟶朱 る蟆企. 螻蠍語 覲企 譟郁 覲旧″蟆 螳. 
-> れ  伎 覯 讌企 蟆.
sql覯 企一朱 貉殊 谿場企 蟆企.

/*------------------------------------------------------------------------------*/
3. 企ろ 碁煙れ 蟆曙 
/*------------------------------------------------------------------------------*/
Status Bits(1 byte) + 讌 (1 byte) + 貉朱れ 襯 谿場 豺(2 byte) + 
1,2,...n覯讌 螻蠍語 貉殊 data + 貉朱れ (2 byte) + null 觜碁У(1 byte) + 
螳覲蠍語 貉殊 (2 byte) + 1,2,...n覯讌 螳覲蠍語 貉殊  豺(2 byte) + 
1,2,...n覯讌 螳覲蠍語 貉 data
/*------------------------------------------------------------------------------*/
--drop table a
create table a (id int, c1 varchar(10), c2 char(10))
create clustered index idx on a(id) --> nonunique clustered index
go
insert into a values (1, 'a', 'a')
insert into a values (1, 'b', 'b')
go
select indid, first from sysindexes where id=object_id('a')

indid  first          
------ -------------- 
1      0x1C0000000100 => 1C000000 : 伎覯, 0100 : 朱

select convert(int, 0x1C)

----------- 
28

dbcc traceon(3604)
dbcc page (tempdb, 1, 28, 1)

Slot 0, Offset 0x60
-------------------
Record Type = PRIMARY_RECORD                        
Record Attributes =  NULL_BITMAP VARIABLE_COLUMNS   
19B1E060:  00120030  00000001  20202061  20202020 0.......a       
19B1E070:  00032020  1b000200  61001c00             .........a

Status Bits(1 byte) : 30
讌 (1 byte) : 00
貉朱れ 襯 谿場 豺(2 byte) : 1200
id貉殊 data(4 byte) : 01000000
c2貉殊 data(10 byte) : 61202020202020202020   --> 襷襦 10 byte螻糾谿讌
貉朱れ (2 byte) : 0300
null 觜碁У(1 byte) : 00
螳覲蠍語 貉殊 (2 byte) : 0200   --> Why 2螳手 ?
nonunique clustered index 願鍵 覓語 覿譯朱 int 貉殊  豺(2 byte) : 1b00
c1 貉殊  豺(2 byte) : 1c00
nonunique clustered index 願鍵 覓語 覿譯朱 int 貉 data :  --> 讌 譴覲給讌 蠍 覓
c1 貉 data : 61

Slot 1, Offset 0x7c
-------------------
Record Type = PRIMARY_RECORD                        
Record Attributes =  NULL_BITMAP VARIABLE_COLUMNS   
19B1E07C:  00120030  00000001  20202062  20202020 0.......b       
19B1E08C:  00032020  1f000200  01002000  62000000   ....... .....b

Status Bits(1 byte) : 30
讌 (1 byte) : 00
貉朱れ 襯 谿場 豺(2 byte) : 1200
id貉殊 data(4 byte) : 01000000
c2貉殊 data(10 byte) : 61202020202020202020   --> 襷襦 10 byte螻糾谿讌
貉朱れ (2 byte) : 0300
null 觜碁У(1 byte) : 00
螳覲蠍語 貉殊 (2 byte) : 0200   --> Why 2螳手 ?
nonunique clustered index 願鍵 覓語 覿譯朱 int 貉殊  豺(2 byte) : 1f00
覯讌 螳覲蠍語 貉殊  豺(2 byte) : 2000
nonunique clustered index 願鍵 覓語 覿譯朱 int 貉 data : 01000000 
                                         --> 譴覲給伎 int朱 1 豢螳蟆
                                         --> int 螻蠍語 貉殊企 願化磯 螳覲蠍語 貉殊朱 豌襴(?)
c1 貉 data : 62

/*------------------------------------------------------------------------------*/
企ろ 碁煙るゼ 襷る 伎 レ り 
襷襦 覓朱Μ朱  螳 ?

--drop table a
create table a (id int, c1 char(10))
create clustered index idx on a(id)  --> nonunique clustered index
go
insert into a values (2, 'a')
insert into a values (1, 'b')
insert into a values (3, 'c')
go
select indid, first from sysindexes where id=object_id('a')

indid  first          
------ -------------- 
1      0x1C0000000100 => 1C000000 : 伎覯, 0100 : 朱

select convert(int, 0x1C)
----------- 
28

dbcc traceon(3604)
dbcc page (tempdb, 1, 28, 1)

DATA:
-----

Slot 0, Offset 0x75
-------------------
Record Type = PRIMARY_RECORD                        
Record Attributes =  NULL_BITMAP                    
19B1E075:  00120010  00000001  20202062  20202020 ........b       
19B1E085:  00022020        00                       ...

Slot 1, Offset 0x60
-------------------
Record Type = PRIMARY_RECORD                        
Record Attributes =  NULL_BITMAP                    
19B1E060:  00120010  00000002  20202061  20202020 ........a       
19B1E070:  00022020        00                       ...

Slot 2, Offset 0x8a
-------------------
Record Type = PRIMARY_RECORD                        
Record Attributes =  NULL_BITMAP                    
19B1E08A:  00120010  00000003  20202063  20202020 ........c       
19B1E09A:  00022020        00                       ...

OFFSET TABLE:
-------------
Row - Offset              
2 (0x2) - 138 (0x8a)      
1 (0x1) - 96 (0x60)       
0 (0x0) - 117 (0x75)      

覓朱Μ  朱朱 ロ 襦 レ .
企ろ一 蟆曙磯 讌 offset table 企ろ れ襦 訖企.

蠏碁る 讌郁 れ ロ覃

delete from a where id=2
go
insert into a values (2, 'aa')
go

dbcc page (tempdb, 1, 28, 1)

DATA:
-----

Slot 0, Offset 0x75
-------------------
Record Type = PRIMARY_RECORD                        
Record Attributes =  NULL_BITMAP                    
19B1E075:  00120010  00000001  20202062  20202020 ........b       
19B1E085:  00022020        00                       ...

Slot 1, Offset 0x9f
-------------------
Record Type = PRIMARY_RECORD                        
Record Attributes =  NULL_BITMAP VARIABLE_COLUMNS   
19B1E09F:  00120030  00000002  20206161  20202020 0.......aa      
19B1E0AF:  00022020  1d000100  00000100        00   ...........

Slot 2, Offset 0x8a
-------------------
Record Type = PRIMARY_RECORD                        
Record Attributes =  NULL_BITMAP                    
19B1E08A:  00120010  00000003  20202063  20202020 ........c       
19B1E09A:  00022020        00                       ...

OFFSET TABLE:
-------------
Row - Offset              
2 (0x2) - 138 (0x8a)      
1 (0x1) - 159 (0x9f)      
0 (0x0) - 117 (0x75)  

 data螳 襴襯  蟆 螻 るジ襴り data襯 j規 offset襷 .
覓朱, 螻伎 讌り れ kる慨覃 語螳(?) 螻糾 .

/*------------------------------------------------------------------------------*/
-- index page リ規譟
/*------------------------------------------------------------------------------*/
1. 螻蠍語 碁煙(企ろ一手化)
/*------------------------------------------------------------------------------*/
   status(1)-螻蠍語企一危-覿襷伎誤(4)-覿襷id(2)-覿襷slot#(2)
   - Ncol(2)-null觜碁У(1)
/*------------------------------------------------------------------------------*/
--drop table a
create table a (id int identity, c1 char(10), c2 varchar(10))
go
insert into a values ('a','1')
insert into a values (null,'2')
go
create index idx1 on a (c1)
go

select indid, first from sysindexes where id=object_id('a')

indid  first          
------ -------------- 
0      0x0F0000000100
2      0x1C0000000100

select convert(int, 0x1c)
----------- 
28

dbcc traceon(3604)
dbcc page (tempdb, 1, 28, 1)

Slot 0, Offset 0x60
-------------------
Record Type = INDEX_RECORD                          
Record Attributes =  NULL_BITMAP                    
19B1E060:  00000016  00000000  0f000000  01000000 ................
19B1E070:  03000100      0100                     ......
--> null 手化一 碁煙 伎 螻糾 谿讌.

Slot 1, Offset 0x76
-------------------
Record Type = INDEX_RECORD                          
Record Attributes =  NULL_BITMAP                    
19B1E076:  20206116  20202020  0f202020  01000000 .a         .....
19B1E086:  03000000      0000                     ......

tag(1byte)                 -> 16
c1貉(10byte)             -> 61202020202020202020
data 伎 豺(4byte)    -> 0f000000
覿襷 伎id(2byte) -> 0100
覿襷 slot#(2byte)        -> 0000
Ncol(2 byte)               -> 0300
null 觜碁У(1 byte)        -> 00

/*------------------------------------------------------------------------------*/
2. 螳覲蠍語 碁煙(企ろ一手化)
/*------------------------------------------------------------------------------*/
   status(1)-螻蠍語企一危-覿襷伎誤(4)-覿襷id(2)-覿襷slot#(2)
   -Ncol(2)-null觜碁У(1)-varchar貉殊 螳(2)-1,2,...n覯讌 varchar貉殊 譬襭 誤(2)
   -1,2,...n覯讌 varchar貉殊 data
/*------------------------------------------------------------------------------*/
--drop table a
create table a (id int identity, c1 char(10), c2 varchar(10))
go
insert into a values ('a','1')
insert into a values ('b',null)
go
create index idx1 on a (c2)
go

select indid, first from sysindexes where id=object_id('a')

indid  first          
------ -------------- 
0      0x0F0000000100
2      0x1C0000000100

select convert(int, 0x1c)
----------- 
28

dbcc traceon(3604)
dbcc page (tempdb, 1, 28, 1)

Slot 0, Offset 0x60
-------------------
Record Type = INDEX_RECORD                          
Record Attributes =  NULL_BITMAP                    
19B1E060:  00000f16  01000100  01000300           ............

tag(1byte)                 -> 16
data 伎 豺(4byte)    -> 0f000000
覿襷 伎id(2byte) -> 0100
覿襷 slot#(2byte)        -> 0100
Ncol(2 byte)               -> 0300
null 觜碁У(1 byte)        -> 01
--> null 手化一 碁煙 伎 覿襷 覲企 螳讌.

Slot 1, Offset 0x6c
-------------------
Record Type = INDEX_RECORD                          
Record Attributes =  NULL_BITMAP VARIABLE_COLUMNS   
19B1E06C:  00000f36  00000100  00000300  00110001 6...............
19B1E07C:        31                               1    

tag(1byte)                 -> 36
data 伎 豺(4byte)    -> 0f000000
覿襷 伎id(2byte) -> 0100
覿襷 slot#(2byte)        -> 0000
Ncol(2 byte)               -> 0300
null 觜碁У(1 byte)        -> 00
varchar貉殊 螳(2byte)  -> 0100
c2(豌覯讌 varchar)貉殊 譬襭 誤(2byte) -> 1100
c2(豌覯讌 varchar)貉殊 data  -> 31

/*------------------------------------------------------------------------------*/
3. 螳覲蠍語+螻蠍語 碁煙(企ろ一手化)
/*------------------------------------------------------------------------------*/
   status(1)-螻蠍語企一危-覿襷伎誤(4)-覿襷id(2)-覿襷slot#(2)-Ncol(2)
   -null 觜碁У(1)-varchar count(2)-1,2,...n覯讌 varchar螳  豺-1,2,...n覯讌 varchar data
/*------------------------------------------------------------------------------*/
--drop table a
create table a (id int identity, c1 char(10), c2 varchar(10))
go
insert into a values ('a','1')
insert into a values (null,'2')
insert into a values ('c',null)
insert into a values (null,null)
go
create index idx1 on a (c2, c1)
go

select indid, first from sysindexes where id=object_id('a')

indid  first          
------ -------------- 
0      0x0F0000000100
2      0x1C0000000100

select convert(int, 0x1c)
----------- 
28

(1螳  )

dbcc traceon(3604)
dbcc page (tempdb, 1, 28, 1)

Slot 0, Offset 0x60   --c1, c2  null
-------------------
Record Type = INDEX_RECORD                          
Record Attributes =  NULL_BITMAP                    
19B1E060:  00000016  00000000  0f000000  01000000 ................
19B1E070:  04000300      0300                     ......

tag(1byte)                 -> 16
c1貉(10byte)             -> 00000000000000000000
data 伎 豺(4byte)    -> 0f000000
覿襷 伎id(2byte) -> 0100
覿襷 slot#(2byte)        -> 0300
ncol                       -> 0400
null 貉殊 BITMAP(1byte)  -> 03

Slot 1, Offset 0x76 --c2襷 null
-------------------
Record Type = INDEX_RECORD                          
Record Attributes =  NULL_BITMAP                    
19B1E076:  20206316  20202020  0f202020  01000000 .c         .....
19B1E086:  04000200      0100                     ......

tag(1byte)                 -> 16
c1貉(10byte)             -> 63202020202020202020
data 伎 豺(4byte)    -> 0f000000
覿襷 伎id(2byte) -> 0100
覿襷 slot#(2byte)        -> 0200
ncol                       -> 0400
null 貉殊 BITMAP(1byte)  -> 01

Slot 2, Offset 0x8c --c1,c2  data  蟆曙
-------------------
Record Type = INDEX_RECORD                          
Record Attributes =  NULL_BITMAP VARIABLE_COLUMNS   
19B1E08C:  20206136  20202020  0f202020  01000000 6a         .....
19B1E09C:  04000000  00010000    31001b           ..........1

tag(1byte)                 -> 36
c1貉(10byte)             -> 61202020202020202020
data 伎 豺(4byte)    -> 0f000000
覿襷 伎id(2byte) -> 0100
覿襷 slot#(2byte)        -> 0000
ncol                       -> 0400
null 貉殊 BITMAP(1byte)  -> 00
varchar貉殊 螳(2byte)  -> 0100
c2(豌覯讌 varchar)貉殊 譬襭 誤(2byte) -> 1b00
c2(豌覯讌 varchar)貉殊 data  -> 31

Slot 3, Offset 0xa7 --c1襷 null
-------------------
Record Type = INDEX_RECORD                          
Record Attributes =  NULL_BITMAP VARIABLE_COLUMNS   
19B1E0A7:  00000036  00000000  0f000000  01000000 6...............
19B1E0B7:  04000100  00010200    32001b           ..........2

tag(1byte)                 -> 36
c1貉(10byte)             -> 00000000000000000000
data 伎 豺(4byte)    -> 0f000000
覿襷 伎id(2byte) -> 0100
覿襷 slot#(2byte)        -> 0100
ncol                       -> 0400
null 貉殊 BITMAP(1byte)  -> 02
varchar貉殊 螳(2byte)  -> 0100
c2(豌覯讌 varchar)貉殊 譬襭 誤(2byte) -> 1b00
c2(豌覯讌 varchar)貉殊 data  -> 32

/*------------------------------------------------------------------------------*/
-- 4. 螻蠍語 碁煙(螻蠍語 企ろ 碁煙り 蟆曙)
/*------------------------------------------------------------------------------*/
--drop table a
create table a(id int identity primary key, c1 char(10), c2 varchar(10))
go
insert into a values ('a','1')
insert into a values ('b','2')
insert into a values ('c','3')
go
create unique index idx1 on a (c1)
go

select indid, first from sysindexes where id=object_id('a')

indid  first          
------ -------------- 
1      0x1C0000000100
2      0x1D0000000100

select convert(int, 0x1d)
----------- 
29

dbcc traceon(3604)
dbcc page (tempdb, 1, 29, 1)

Slot 0, Offset 0x60
-------------------
Record Type = INDEX_RECORD                          
Record Attributes =  NULL_BITMAP                    
19B20060:  20206116  20202020  01202020  02000000 .a         .....
19B20070:      0000                               ..

tag -> 16
c2  -> 61202020202020202020
螻蠍語 企ろ壱 -> 01000000
ncol-> 0200
null-> 00

/*------------------------------------------------------------------------------*/
-- 5. 螳覲蠍語 碁煙(螻蠍語 企ろ 碁煙り 蟆曙)
/*------------------------------------------------------------------------------*/
--drop table a
create table a(id int identity primary key, c1 char(10), c2 varchar(10))
go
insert into a values ('a','1')
insert into a values ('b','2')
insert into a values ('c','3')
go
create unique index idx1 on a (c2)
go

select indid, first from sysindexes where id=object_id('a')

indid  first          
------ -------------- 
1      0x1C0000000100
2      0x1D0000000100

select convert(int, 0x1d)
----------- 
29

dbcc traceon(3604)
dbcc page (tempdb, 1, 29, 1)

Slot 0, Offset 0x60
-------------------
Record Type = INDEX_RECORD                          
Record Attributes =  NULL_BITMAP VARIABLE_COLUMNS   
19B20060:  00000136  00000200  000d0001        31 6...........1

tag -> 36
螻蠍語 企ろ壱 -> 01000000
ncol-> 0200
null-> 00
varcnt->0100
1st var end point->0d00
1st varchar data->31

/*------------------------------------------------------------------------------*/
-- 6. 螻蠍語 碁煙(螳覲蠍語 企ろ 碁煙り  蟆曙)
/*------------------------------------------------------------------------------*/
--drop table a
create table a(id int identity, c1 char(10), c2 varchar(10))
go
insert into a values ('a','1')
insert into a values ('b','2')
insert into a values ('c','3')
go
create clustered index idx on a(c2)
create index idx1 on a (c1)
go

select indid, first from sysindexes where id=object_id('a')

indid  first          
------ -------------- 
1      0x1C0000000100
2      0x0F0000000100

select convert(int, 0x0f)
----------- 
15

dbcc traceon(3604)
dbcc page (tempdb, 1, 15, 1)

Slot 0, Offset 0x60
-------------------
Record Type = INDEX_RECORD                          
Record Attributes =  NULL_BITMAP VARIABLE_COLUMNS   
19AD6060:  20206136  20202020  03202020  00010000 6a         .....
19AD6070:    310013                               ..1

tag -> 36
螻蠍語伎碁煙れ錆 -> 61202020202020202020
ncol-> 0300
null-> 00
varcnt->0100
1st var end point->1300
螳覲蠍語 企ろ 碁煙  ->31

/*------------------------------------------------------------------------------*/
-- 7. 螳覲蠍語 碁煙(螳覲蠍語 企ろ 碁煙り  蟆曙)
/*------------------------------------------------------------------------------*/
--drop table a
create table a(id varchar(4), c1 char(10), c2 varchar(10))
go
insert into a values ('9', 'a','1')
insert into a values ('8', 'b','2')
insert into a values ('7', 'c','3')
go
create clustered index idx on a(id)
create index idx1 on a (c2)
go
select indid, first from sysindexes where id=object_id('a')

indid  first          
------ -------------- 
1      0x1C0000000100
2      0x0F0000000100

select convert(int, 0x0f)
----------- 
15

dbcc traceon(3604)
dbcc page (tempdb, 1, 15, 1)

Slot 0, Offset 0x60
-------------------
Record Type = INDEX_RECORD                          
Record Attributes =  NULL_BITMAP VARIABLE_COLUMNS   
19AD6060:  00000336  000b0002  3931000c           6.........19

tag -> 36
ncol-> 0300
null-> 00
varcnt->0200
1st var end point->0b00
2st var end point->0c00
螳覲蠍語 碁煙 ->31
螳覲蠍語 企ろ 碁煙 ->39

/*------------------------------------------------------------------------------*/
-- 8. 螻蠍語 碁煙(螻蠍語 企ろ 碁煙り  蟆曙) - 企ろ 碁煙れ 貉殊覲旧
/*------------------------------------------------------------------------------*/
--drop table a
create table a(id int, c1 char(10), c2 varchar(10))
go
insert into a values (9, 'a','1')
insert into a values (8, 'b','2')
insert into a values (7, 'c','3')
go
create clustered index idx on a(id)
create index idx1 on a (id,c1)
go
select indid, first from sysindexes where id=object_id('a')

indid  first          
------ -------------- 
1      0x1C0000000100
2      0x0F0000000100

select convert(int, 0x0f)
----------- 
15

dbcc traceon(3604)
dbcc page (tempdb, 1, 15, 1)

Slot 0, Offset 0x60
-------------------
Record Type = INDEX_RECORD                          
Record Attributes =  NULL_BITMAP                    
19AD6060:  00000716  20206300  20202020  03202020 .....c         .
19AD6070:      0000                               ..

tag -> 16
螻蠍語 企ろ 碁煙 ->07000000
螻蠍語 碁煙 ->63202020202020202020
ncol-> 0300
null-> 00

/*------------------------------------------------------------------------------*/
-- 9. 螳覲蠍語 碁煙(螻蠍語 企ろ 碁煙り  蟆曙) - 企ろ 碁煙れ 貉殊覲旧
/*------------------------------------------------------------------------------*/
--drop table a
create table a(id int, c1 char(10), c2 varchar(10))
go
insert into a values (9, 'a','1')
insert into a values (8, 'b','2')
insert into a values (7, 'c','3')
go
create clustered index idx on a(id)
create index idx1 on a (id,c2)
go
select indid, first from sysindexes where id=object_id('a')

indid  first          
------ -------------- 
1      0x1C0000000100
2      0x0F0000000100

select convert(int, 0x0f)
----------- 
15

dbcc traceon(3604)
dbcc page (tempdb, 1, 15, 1)

Slot 0, Offset 0x60
-------------------
Record Type = INDEX_RECORD                          
Record Attributes =  NULL_BITMAP VARIABLE_COLUMNS   
19AD6060:  00000736  00000300  000d0001        33 6...........3

tag -> 36
螻蠍語 企ろ 碁煙 ->07000000
ncol-> 0300
null-> 00
varcnt->0100
1st var end point->0d00
varchar企ろ壱->33

/*------------------------------------------------------------------------------*/
-- 10. 螻蠍語 碁煙(螳覲蠍語 企ろ 碁煙り  蟆曙) - 企ろ 碁煙れ 貉殊覲旧
/*------------------------------------------------------------------------------*/
--drop table a
create table a(id varchar(4), c1 char(10), c2 varchar(10))
go
insert into a values ('9', 'a','1')
insert into a values ('8', 'b','2')
insert into a values ('7', 'c','3')
go
create clustered index idx on a(id)
create index idx1 on a (id,c1)
go
select indid, first from sysindexes where id=object_id('a')

indid  first          
------ -------------- 
1      0x1C0000000100
2      0x0F0000000100

select convert(int, 0x0f)
----------- 
15

dbcc traceon(3604)
dbcc page (tempdb, 1, 15, 1)

Slot 0, Offset 0x60
-------------------
Record Type = INDEX_RECORD                          
Record Attributes =  NULL_BITMAP VARIABLE_COLUMNS   
19AD6060:  20206336  20202020  03202020  00010000 6c         .....
19AD6070:    370013                               ..7

tag -> 36
螻蠍語 碁煙 ->63202020202020202020
ncol-> 0300
null-> 00
varcnt->0100
1st var end point->1300
varchar企ろ壱->37

/*------------------------------------------------------------------------------*/
-- 11. 螳覲蠍語 碁煙(螳覲蠍語 企ろ 碁煙り  蟆曙) - 企ろ 碁煙れ 貉殊覲旧
/*------------------------------------------------------------------------------*/
--drop table a
create table a(id varchar(4), c1 char(10), c2 varchar(10))
go
insert into a values ('9', 'a','1')
insert into a values ('8', 'b','2')
insert into a values ('7', 'c','3')
go
create clustered index idx on a(id)
create index idx1 on a (id,c2)
go
select indid, first from sysindexes where id=object_id('a')

indid  first          
------ -------------- 
1      0x1C0000000100
2      0x0F0000000100

select convert(int, 0x0f)
----------- 
15

dbcc traceon(3604)
dbcc page (tempdb, 1, 15, 1)

Slot 0, Offset 0x60
-------------------
Record Type = INDEX_RECORD                          
Record Attributes =  NULL_BITMAP VARIABLE_COLUMNS   
19AD6060:  00000336  000b0002  3337000c           6.........73

tag -> 36
ncol-> 0300
null-> 00
varcnt->0200
1st var end point->0b00
1st var end point->0c00
id data->37
c2 data->33

/*------------------------------------------------------------------------------*/
-- 12. 螳覲蠍語+螻蠍語 碁煙(螳覲蠍語+螻蠍語 企ろ 碁煙り 蟆曙)
/*------------------------------------------------------------------------------*/
--drop table a
create table a
(id varchar(4), c1 char(10), c2 varchar(10), c3 char(10), primary key(id,c1))
go
insert into a values ('9', 'a','1','e')
insert into a values ('8', 'b','2','f')
insert into a values ('7', 'c','3','g')
go
create index idx1 on a (c2,c3)
go
select indid, first from sysindexes where id=object_id('a')
indid  first          
------ -------------- 
1      0x1C0000000100
2      0x1D0000000100

select convert(int, 0x1d)
----------- 
29

dbcc traceon(3604)
dbcc page (tempdb, 1, 29, 1)

Slot 0, Offset 0x60
-------------------
Record Type = INDEX_RECORD                          
Record Attributes =  NULL_BITMAP VARIABLE_COLUMNS   
19B20060:  20206536  20202020  61202020  20202020 6e         a    
19B20070:  20202020  00000420  001f0002  39310020      ....... .19

tag -> 36
螻蠍語伎碁煙ろ->65202020202020202020
螻蠍語危企ろ壱->61202020202020202020
ncol-> 0400
null-> 00
varcnt->0200
1st var end point->1f00
2nd var end point->2000
varchar碁煙ろ->31
varchar企ろ壱->39

/*------------------------------------------------------------------------------*/
-- 覿襦 : data type覲 リ規譟
/*------------------------------------------------------------------------------*/
char, varchar, int, datetime, smalldatetime, decimal, float, tinyint, bit   蟲譟磯ゼ 覲伎.
--drop table a
create table a(c1 char(4), c2 varchar(4), c3 int, c4 datetime,
               c5 smalldatetime, c6 decimal(10,2), c7 float,
               c8 tinyint, c9 bit)
go
insert into a values ('ab', 'ab', 3, getdate(), getdate(), 6.0, 7.0, 8, 1)
go
select * from a

c1   c2   c3    c4                        c5                     c6      c7      c8   c9   
---- ---- ----- ------------------------- ---------------------- ------- ------- ---- ---- 
ab   ab   3     2003-03-31 14:12:20.200   2003-03-31 14:12:00    6.00    7.0     8    1

select convert(varchar(10),name) name, colid, xoffset, length from syscolumns where id=object_id('a')

name       colid  xoffset length 
---------- ------ ------- ------ 
c1         1      4       4
c2         2      -1      4
c3         3      8       4
c4         4      12      8
c5         5      20      4
c6         6      24      9
c7         7      33      8
c8         8      41      1
c9         9      42      1

select indid, first from sysindexes where id=object_id('a')

indid  first          
------ -------------- 
0      0x1C0000000100

select convert(int, 0x1C)
----------- 
28

dbcc traceon(3604)
dbcc page (tempdb, 1, 28, 1)

Slot 0, Offset 0x60
-------------------
Record Type = PRIMARY_RECORD                        
Record Attributes =  NULL_BITMAP VARIABLE_COLUMNS   
19EBC060:  002b0030  20206261  00000003  00ea19ec 0.+.ab  ........
19EBC070:  0000934d  934d0354  00025801  00000000 M...T.M..X......
19EBC080:  00000000  1c000000  09010840  01000000 ........@.......
19EBC090:  61003500        62                     .5.ab

--襷讌襷朱 覿企慨覃
Status Bits(1 byte)                  : 30
讌 (1 byte)                : 00
貉朱れ 襯 谿場 豺(2 byte)      : 002b
c1貉(char) data(4 byte)          : 61622020           --ab ( space襯 蟲 蟲)
c3貉(int) data(4 byte)           : 00000003           --3
c4貉(datetime) data(8 byte)      : 0000934d00ea19ec   --2003-03-31 14:12:20.200
c5貉(smalldatetime) data(4 byte) : 934d0354           --2003-03-31 14:12:00
c6貉(decimal) data(9 byte)       : 000000000000025801 --6.00
c7貉(float) data(8 byte)         : 0000000000001c40   --7.0
c8貉(tinyint) data(1 byte)       : 08                 --8
c9貉(bit) data(1 byte)           : 01                 --1 (1bit貉 8螳蟾讌 1byte襦 豌襴)
貉朱れ (2 byte)                  : 0009
null 觜碁У(2 byte)                  : 0000               --貉殊 9螳 企襦 2byte
螳覲蠍語 貉殊 (2 byte)           : 0001
c2貉(varchar)  豺(2 byte): 0035
c2貉(varchar) data                 : 6162               --ab (space )

/*------------------------------------------------------------------------------*/
蟆磯 : れ 碁一 蠍語伎. 蠏碁蟲, 襴  . 

data, index page 螻牛旧 
   1. 螻蠍語 貉殊 蟆曙 null  レ  朱 覓伎^蟇 螻糾 谿讌.
   2. 螳覲蠍語 貉殊 蟆曙 null  蟆曙 螻糾 谿讌讌 .
   3. 企 語 讌伎 襦 ル讌 .
      -> 螻蠍語 貉殊 朱 覈願, 螳覲蠍語 貉殊 る 覈碁.

data page  蟆曙
   1. 企ろ 碁煙り  蟆曙
      -> 螻蠍語 貉殊 syscolumns 企 offset 螳朱 企 豺襯 覦襦 谿場 .      
      -> 螳覲蠍語 貉殊 ? 
         -> syscolumns 企 offset 螳朱 n覯讌 螳覲蠍語 貉殊企朱 覲企ゼ 詞
            n覯讌 螳覲蠍語 貉殊  豺襯 谿場伎伎 蠏瑚崖 data襯 蟶朱 .

   2. 企ろ 碁煙り  蟆曙
      -> data page豌願 覓朱Μ朱 讌 . る offset table .
      -> nonunique  蟆曙 unique 螳 襷れ 譯手鍵 伎 int 貉殊 覿碁.
         蠏碁, int 豌企 螻蠍語 企 企ろ 碁煙れ 覿企 int 貉殊
         螳覲蠍語 貉殊朱 豌襴 れ曙 .

index page 蟆曙
   1. 企ろ 碁煙り  蟆曙
      1) 覿襷 伎襯 螳讌.(伎誤(4)-id(2)-slot(2))
      2) 螳覲蠍語 貉殊 null 手化一 碁煙 伎 覿襷 覲企 螳讌.

   2. 企ろ 碁煙り  蟆曙(企ろ 碁煙)
      1) 覿襷 伎 螻  企ろ 碁煙れ り 螳讌.
      2) 螳覲蠍語 貉殊 null 手化一 碁煙 伎 企ろ 碁煙れ  覲企 螳讌.
      3) 企ろ 碁煙 貉殊 覓伎^蟇 企ろ 碁煙れ .
         朱朱 企ろ 碁煙 貉 + 企ろ 碁煙 貉 企一朱 ル讌襷
         蠏碁, 碁煙 煙 伎襦 企ろ 碁煙れ 企ろ 碁煙 貉殊
         譴螳 l伎 誤 蟆曙磯 語朱 企ろ 碁煙るゼ 襷る 讌 襦 
         襷れ伎.(, 螻蠍語  螳覲蠍語企 狩 蟆曙一襷, 蠏碁讌  蟆曙磯 螻蠍語
         貉殊  り 螳覲蠍語 貉殊 れ .)

蟠, 蟆磯譟一姶 蟲一... 願姥  蟾 ? - 蠏碁 蟆 蟾  ^o^ ;;;;;

/*------------------------------------------------------------------------------*/

varchar襯 覃 リ概螳 蟆 谿讌覩襦 襷 襭襯 伎 l . 
襷手 ?

炎貉殊碁煙  : varchar襦 誤 るる - 4byte
2貉朱概碁煙 : varchar襦 誤 るる - 6byte
3貉朱概碁煙 : varchar襦 誤 るる - 8byte

螳, 螻蠍語 貉 譟一姶 char襦 れ讌 螻 varchar襦  蟆曙郁 襷 .
る狩伎 覈襯願讌襷, 企 ms-sql襷殊 螻蠍語 貉 讀, 覿覿 貊 貉殊
varchar覲企る char襦 ′譯朱蟆  譬讌 蟾 .
覓語襯   row襷 豕 4byte 伎 るる 豌蟆企.
襷 100襷蟇伎企朱 
1貉 碁煙 : 4 * 1000000 = 4000000 byte = 3096 kb = 3 mb = 488 page
2貉 碁煙 : 6 * 1000000 = 6000000 byte = 5859 kb = 5 mb = 732 page
3貉 碁煙 : 8 * 1000000 = 8000000 byte = 7812 kb = 7 mb = 976 page
る 豌 るる企.

/*------------------------------------------------------------------------------*/
轟, 覲企 誤 伎 覃 Inside SQL Server 2000企朱 豈 覲伎讌,
 磯殊 螳 螳譬(SQL Server 2000 Internal - 襭) .