SQL Server 2008의 새로운 물리적 행 위치 함수

해외블로그에 SQL Server 2008 관련하여 재미있는 기능이 있어 소개합니다.

SQL Server 2008 CTP6에서 제공하는 비문서화된 함수를 사용하여 데이터 조회시 각각의 행에 대해서 위치하고 있는 RID정보를 확인 할 수 있으며, 변경, 삭제시에도 행의 물리적 위치를 기반으로 데이터를 수정 할 수 있습니다.

하지만 RID기반으로 데이터를 조회시 RID 주소를 이용하여 데이터를 접근하는 것이 아닌 테이블을 SCAN 후 필터조건으로 빠지고 있습니다.
이렇게 되면, 그다지 의미가 있지 않을 것 같은데~ ㅡㅡ;

자세한 내용은 아래 테스트를 참조하세요.
SQL Server 2008: New (undocumented) physical row locator function

--버전
select @@VERSION
Microsoft SQL Server code name "Katmai" (CTP) - 10.0.1300.13 (Intel X86) Feb 8 2008 00:06:52
Copyright (c) 1988-2007 Microsoft Corporation Developer Edition on Windows NT 5.2 <X86> (Build 3790: Service Pack 2)

--관련 함수 찾기
select name,type_desc from sys.all_objects
where name like '%physloc%'

/*
두가지 함수의 소스 확인
바이너리 데이터를 RID형태로 변경해주는 함수, 테이블 함수
*/

EXEC SP_HELPTEXT 'sys.fn_PhysLocCracker'
EXEC SP_HELPTEXT 'sys.fn_PhysLocFormatter'

/*
테스트 시작
*/

--기존 인덱스 및 테이블을 지우자.
DROP INDEX tbl1.cl_tbl1,tbl2.cl_tbl2
DROP TABLE tbl1,tbl2,tbl3
GO

--테스트 테이블 만들기
SELECT TOP 1000
 ROW_NUMBER() OVER(ORDER BY (SELECT 1)) AS COL1
,CAST('HYOKSONG' AS CHAR(1000))AS COL2
INTO TBL1
FROM SYS.SYSINDEXES A,SYS.SYSINDEXES B

SELECT TOP 1000
ROW_NUMBER() OVER(ORDER BY (SELECT 1)) AS COL1
,CAST('HYOKSONG' AS CHAR(1000))AS COL2
INTO TBL2
FROM SYS.SYSINDEXES A,SYS.SYSINDEXES B

SELECT TOP 100
ROW_NUMBER() OVER(ORDER BY (SELECT 1)) AS COL1
,REPLICATE(CAST('HYOKSONG' AS VARCHAR(MAX)),10000)AS COL2
INTO TBL3
FROM SYS.SYSINDEXES A,SYS.SYSINDEXES B

--인덱스를 생성
create unique clustered index cl_tbl1 on tbl1(col1)
create unique clustered index cl_tbl2 on tbl2(col1)

--바이너리 형태로 물리 위치정보를 확인
select %%physloc%%,* from tbl1;

--시스템 함수를 사용하여 fileid,pageid,slotid를 확인.
SELECT TOP 10 sys.fn_PhysLocFormatter(%%physloc%%) AS [Physical RID], * FROM tbl1;

--테이블 함수를 apply해서 확인
select TOP 10 * from tbl1 cross apply sys.fn_PhysLocCracker(%%physloc%%)B

-- 조인시 A.%%physloc%% 이렇게 별칭을 주어 각각의 테이블에 대한 물리적 정보를 확인 select TOP 10 A.%%physloc%%,B.%%physloc%%,*
from tbl1 A inner join tbl2 B on A.col1 = B.col1

-- 그러면, 페이지 별로 몇 개의 데이터가 있는지 확인? 그리고 해당 페이지에 최소, 최대의 키값이 무엇인지?
select 
    cast(convert (binary (2), reverse (substring (%%physloc%%, 5, 2))) as int) as fileid
   ,cast(convert (binary (4), reverse (substring (%%physloc%%, 1, 4))) as int) as pageid
    ,COUNT(*) as PagesCnt
    ,MIN(col1) as minkey
    ,MAX(col1) as minkey
FROM tbl1
group by 
    cast(convert (binary (2), reverse (substring (%%physloc%%, 5, 2))) as int)
    ,cast(convert (binary (4), reverse (substring (%%physloc%%, 1, 4))) as int)
ORDER BY 1,2

-- 페이지 정보를 기반으로 해당 페이지에 있는 모든 데이터를 변경.
update tbl1
set col2 = ''
where %%physloc%% = 0x1003000001000000

-- 페이지 정보를 기반으로 해당 페이지에 있는 모든 데이터를 삭제
delete tbl1
where %%physloc%% = 0x1003000001000000

--그렇다면 LOB 타입의위치정보는?
???

송 혁, SQL Server MVP
sqler.pe.kr
nexondbteam.tistory.com