#title 물리구조 {{{ 출처: 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 - 유료)도 있음. }}}