강의 컨설팅 트레이닝 무료진단 무료책자 마케팅편지 마케팅정보공유 다이어리 서비스제휴 고객센터

데이터가 저장되는 형태와 인덱스 페이지
작성자 : 13 김영철
등록날짜 : 2009.01.23 01:31
1,394

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

 

download.asp?seq=7858&part=myboard4&file


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
실로 엄청난 오버헤드이다.

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

[출처] 메롱

"쇼핑몰·홈페이지·오픈마켓
블로그·페이스북·이메일 등의 각종 마케팅 글쓰기,
각종 광고, 영업, 판매, 제안서, 전단지
반응율 3배×10배 이상 높이는 마법의 8단계 공식"
자세히보기

Comments

번호 제목 글쓴이 날짜 조회
2565 문자열에서 단어 분리 - SQL Server 2005 M 최고의하루 12.23 2396
2564 25가지 SQL작성법-3 M 최고의하루 12.23 2132
2563 25가지 SQL작성법-2 M 최고의하루 12.23 2410
2562 25가지 SQL작성법-1 M 최고의하루 12.23 2132
2561 제약조건(1) M 최고의하루 12.20 3644
2560 [MSSQL]SQL Server Management Studio Express M 최고의하루 12.20 2617
2559 Microsoft SQL Server 2005 Express Edition SP2 M 최고의하루 12.19 2330
2558 Microsoft SQL Server Management Studio Express SP2 M 최고의하루 12.19 2407
2557 [MSSQL] 데이터 백업과 복구 ( SQL SERVER ) M 최고의하루 12.18 2686
2556 [ Sybase ] Sybase 기본 명령어 M 최고의하루 12.18 2816
2555 오라클(Oracle) 10g Database 설치 M 최고의하루 12.04 3132
2554 오라클데이터베이스 복구 M 최고의하루 12.04 2199
2553 MSSQL에서 문자로 된 날짜 시간 차이값 얻기 13 김영철 01.24 3711
2552 T-SQL 페이징 구현하기 13 김영철 01.24 2747
열람중 데이터가 저장되는 형태와 인덱스 페이지 13 김영철 01.23 1395
2550 SQL Server 2000의 현재 버전 확인 13 김영철 01.23 2104
2549 유용한 SQL 쿼리 13 김영철 01.23 1875
2548 [MSSQL] 게시판 페이징 쿼리 13 김영철 01.23 2466
2547 게시판에서 페이징 쿼리 13 김영철 01.23 2025
2546 테이블의 레코드 총 개수 얻기 좀 더 빠른 방법 13 김영철 01.23 2025
2545 데이터베이스 내에 있는 모든 테이블의 row 수와 용량 구하기 13 김영철 01.23 2044
2544 저장프로시저 디버깅 준비 13 김영철 01.23 2080
2543 [MS-SQL] Parameters 를 이용한 쿼리실행 13 김영철 01.23 2871
2542 MS SQL 서버 확장 스토어드 프로시저 만들기 13 김영철 01.23 2987
2541 MS-SQL에서 개선된 페이징 쿼리 13 김영철 01.23 2719
2540 MS-SQL Server Transaction Isolation Level 13 김영철 01.23 2595
2539 다른 서버로 DB 백업 받기 13 김영철 01.23 2197
2538 MSSQL 내장 함수 목록 13 김영철 01.23 2134
2537 몇가지 sql 명령어 13 김영철 01.23 2872
2536 명령어정리 13 김영철 01.23 1707
마케팅
특별 마케팅자료
다운로드 마케팅자료
창업,경영
기획,카피,상품전략
동기부여,성취