[2k5] "large value types out of row" 테이블 옵션 확인

1. /**********************************************************************************************
   -- Title : [2k5] "large value types out of row" 테이블 옵션 확인
   -- Reference : mcpworld.com
   -- Key word : sp_tableoption large value types out of row dbcc page forward pointer
   **********************************************************************************************/
   use master;
   go
    
   drop database newdatatype;
   go
    
   create database newdatatype;
   go
    
   use newdatatype;
   go
    
   create table bigtable
   ( id int, col1 varchar(25)
   , col2 varchar(max)
   , col3 varchar(max)
   );
   go
    
   insert into bigtable values (1, 'row1', 'aaaaaaaaaa', 'aaaaaaaaaa');
   insert into bigtable values (2, 'row2', 'bbbbbbbbbb', 'bbbbbbbbbb');
   go
    
   update bigtable set col2 = replicate('a', 8000)
   where id = 1;
   go
    
   update bigtable set col2 = replicate('b', 4000)
   where id = 2;
   go
    
   exec sp_tableoption N'bigtable', 'large value types out of row', 'on';
   go
    
   dbcc page ('newdatatype', 1, 40, 3);
   go
    
   update bigtable set col2 = replicate('a', 20)
   where id = 1;
   go
    
   dbcc page ('newdatatype', 1, 40, 3);
   go
    
   dbcc extentinfo ('newdatatype', 'bigtable');
   go
    
   dbcc page ('newdatatype', 1, 89, 3)
   go
    
   sp_tableoption N'bigtable', 'large value types out of row', 'off';
   go