MS SQL 2005 new Feature - 8KB를 초과하는 행 오버플로 데이터

첫번째 : 2009/10/26 - [MSSQL/SQL Server 2005 new Feature] - MSSQL 2005 new Feature - OPENROWSET
두번째 : 2009/10/28 - [MSSQL/SQL Server 2005 new Feature] - MSSQL 2005 new Feature - 향상된 분산쿼리

벌써 3번째 포스팅입니다. 이번 포스팅에서는 8KB를 초과하는 행 오버플로 데이터에 대해서 알아보도록 하겠습니다. 2006년 당시 8KB라는 압박에 대해서 처음 알았습니다. 그 전까지는 전혀 몰랐습니다. 왜 8KB인가 ?? Microsoft사의 SQL Server의 최소 저장단위는 PAGE 입니다. 한 PAGE당 8KB 입니다. 8KB를 byte로 환산하면 8192byte 입니다. 그중에 Data를 저장할수 있는 공간은 8090byte입니다. 나머지 79byte는 페이지 정보를 저장합니다. 즉, 한 행의 데이터의 크기는 한 페이지를 넘길 수 없었습니다. SQL Server 2000에서는요. *^^*

그럼 SQL Server 2005에서는 어떻게 지원을 하고 있는지 간략하게 예제를 통해 보여드리겠습니다.
해당 예제는 http://sqler.pe.kr/web_board/view_list.asp?id=840&read=1404&pagec=21&gotopage=21&block=2&part=MyBoard7&tip= 에 있는 예제를 발취한 것입니다.

--########################################################
-- 8KB를 초과하는 행 오버플로 데이터 만들기 (ERROR)
--########################################################
BEGIN TRY
	CREATE TABLE tbl_tbly(
		idx INT IDENTITY(1, 1)
	,	a CHAR(8000)
	,	b CHAR(8000)
	,	c CHAR(8000)
	)
END TRY BEGIN CATCH END CATCH

위와 같이 실행을 하면 ?? 아래와 같은 실행 결과를 보실 수 있습니다.

뻘껀 Error 메시지 입니다.
여기서 잠깐!! 근데 여기서에서 TRY ~ CATCH 구문까지 함께 실행을 하면 아무런 반응 없고 "명령이 완료되었습니다." 라고만 출력됩니다. 해당 TRY ~ CATCH 구문은 다른 분께서 올려주실 겁니다. 그때 자세히 보기로 하고요. 아무튼 Error가 납니다. 2009/10/28 - [MSSQL/SQL Server 2005 new Feature] - Try Catch에 대한 고찰...

--########################################################
-- 8KB를 초과하는 행 오버플로 데이터 만들기
--########################################################
BEGIN TRY
	CREATE TABLE tbl_tblx(
		idx INT IDENTITY(1, 1)
	,	a VARCHAR(8000)
	,	b VARCHAR(8000)
	,	c VARCHAR(8000)
	)
END TRY BEGIN CATCH END CATCH

INSERT INTO tbl_tblx VALUES (
		REPLICATE('a', 8000)
	,	REPLICATE('b', 8000)
	,	REPLICATE('c', 8000)
)

select LEN(a),LEN(b),LEN(c) from tbl_tblx

위와 같이 실행 하면 ?? 놀랍게도 아래와 같이 출력 화면을 보실 수 있습니다.

와우 Table도 정상적으로 만들어 진듯 하고, 데이터도 정상적으로 들어 간듯 합니다. 신기합니다. 해당 정보를 좀 봐야 할것 같습니다.

--########################################################
-- 8KB를 초과하는 행 보기
--########################################################
dbcc traceon (3604)
dbcc extentinfo ('TESTDB','tbl_tblx',0)

-- http://msdn.microsoft.com/ko-kr/library/ms188917(SQL.90).aspx
SELECT
	database_id
,	OBJECT_ID
,	index_id
,	partition_number
,	index_type_desc
,	alloc_unit_type_desc
,	index_depth
,	index_level
,	avg_fragmentation_in_percent
,	fragment_count
,	avg_fragment_size_in_pages
,	page_count
,	avg_page_space_used_in_percent
,	record_count
,	ghost_record_count
,	version_ghost_record_count
,	min_record_size_in_bytes
,	max_record_size_in_bytes
,	avg_record_size_in_bytes
,	forwarded_record_count
FROM sys.dm_db_index_physical_stats(
		DB_ID('TESTDB')
	,	OBJECT_ID('tbl_tblx')
	,	NULL
	,	NULL
	,	'DETAILED'
)

이렇게 해당 정보를 볼수 있는 함수를 통해서 해당 Table을 보면 아래과 같은 결과가 출력 됩니다.

"아하" ROW_OVERFLOW_DATA라는 type이 새로 생겼나봅니다. 그렇습니다. SQL Server 2005 부터는 varchar, varbinary, nvarchar, sql_variant에 대해서 오버플로우가 발생했을때, 타입을 ROW_OVERFLOW_DATA로 내부적으로 변경을 하고 IN_ROW_DATA와 다른 저장공간으로 이동 시킨다고 합니다. 그리고 그 데이터가 어떠한 변경으로 인하여 데이터크기가 줄어들었을때는 ROW_OVERFLOW_DATA에서 IN_ROW_DATA로 변경되면서 저장공간도 이동하게 된다고 합니다. 그래서 Microsoft사에서는 되도록이면 쓰지말고 쓰려면 해당 내용을 숙지 후 사용하라고 합니다. 책임지지 않겠다고 합니다. Microsoft사는 항상 그러니깐요 *^^*


다시한번 말씀드리지만 해당 포스팅 내용의 원본은  http://sqler.pe.kr/web_board/view_list.asp?id=840&read=1404&pagec=21&gotopage=21&block=2&part=MyBoard7&tip= 여기 이고 제가 임의적으로 인용한 것입니다.