豢豌: 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襯 ｊ規 offset襷 .
覓朱, 螻伎 讌り れ ｋる慨覃 語螳(?) 螻糾 .
/*------------------------------------------------------------------------------*/
-- 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) 企ろ 碁煙 貉殊 覓伎＾蟇 企ろ 碁煙れ .
朱朱 企ろ 碁煙 貉 + 企ろ 碁煙 貉 企一朱 ル讌襷
蠏碁, 碁煙 煙 伎襦 企ろ 碁煙れ 企ろ 碁煙 貉殊
譴螳 ｌ伎 誤 蟆曙磯 語朱 企ろ 碁煙るゼ 襷る 讌 襦
襷れ伎.(, 螻蠍語 螳覲蠍語企 狩 蟆曙一襷, 蠏碁讌 蟆曙磯 螻蠍語
貉殊 り 螳覲蠍語 貉殊 れ .)
蟠, 蟆磯譟一姶 蟲一... 願姥 蟾 ? - 蠏碁 蟆 蟾 ^o^ ;;;;;
/*------------------------------------------------------------------------------*/
varchar襯 覃 リ概螳 蟆 谿讌覩襦 襷 襭襯 伎 ｌ .
襷手 ?
炎貉殊碁煙 : 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 - 襭) .