《Microsoft Sql server 2008 Internal》读书笔记--第七章Special Storage(2)

《Microsoft Sql server 2008 Interna》读书笔记订阅地址:
http://www.cnblogs.com/downmoon/category/230397.html/rss

《Microsoft Sql server 2008 Interna》索引目录:

《Microsoft Sql server 2008 Internal》读书笔记--目录索引

上文简单介绍了限制长度的Large Object Data存储形式,本文将介绍不限长度的Large Object Data存储形式。

不限长度的大对象数据

 如果一个表包含老的数据类型如text/ntext/image等,默认的实际数据是没有存储在常规数据页,与行溢出数据类似,LOB也存储在自相关的页面中。对LOB列,SQL Server存储一个16字节的指针指向数据的实际页。不过,SQL Server允许你修改默认表选项,以使LOB数据存储在数据行中,只要数据足够小。注意只有表选项,没有数据库或服务器选项来设置。

 默认的LOB数据没有存储在数据行,数据行仅仅存储了16字节的指针。指针指向的页是8kb大小。一个text/ntext/image页可能存放来自多个列或多个行的数据,但必须来自同一个单表。

这些组成LOB列的8KB页的集合并不需要位置相邻。页面在b树结构中是逻辑排序的。因此,操作LOB中间开始的部分效率最高。

邀月工作室 

 如果LOB列的数据小于32kB,数据行的文本指针指向一个84字节的文本根结构,这构成了B树结构的根节点。这个根节点指向 text.ntext.image页。尽管LOB数据在b树结构中是逻辑排序,但是在根节点和所有的独立数据块中是在整个表的LOB页中物理扩展,且大小不定。如果数据小于64字节,将被存放在根结构中。

如果大于32kb,SQL Server开始在根节点有和数据块之间建立一些中间节点,中间节点的数据页并不共享,即每个存储中间节点的页中仅仅包含一个数据行的某个text或image的列的中间节点。

SQL Server能存储LOB根和LOB实际数据在两种不同类型的页。如果其中的一种,引用为Text_Mixed,允许来自多行的LOB数据共享同一个页。然而,一旦文本数据大于40KB,SQL Server开始将整个页投入到一个单个的LOB值。这个页被引用作Text_data页。

我们看一个例子:

IF EXISTS (SELECT * FROM sys.tables        
            
WHERE name = 'textdata')
    
DROP TABLE textdata;
GO
CREATE TABLE textdata
 (bigcol 
text);
GO
INSERT INTO textdata
   
SELECT REPLICATE(convert(varchar(MAX), 'a'), 38000);
GO
TRUNCATE TABLE sp_tablepages;
GO
INSERT INTO sp_tablepages
  
EXEC('DBCC IND(testdb, textdata, -1)');
GO
SELECT PageFID, PagePID, ObjectID, IAM_chain_type, PageType
FROM sp_tablepages;
GO

 

邀月工作室

INSERT INTO textdata
   
SELECT REPLICATE(convert(varchar(MAX), 'a'), 41000);
GO
TRUNCATE TABLE sp_tablepages;
GO
INSERT INTO sp_tablepages
  
EXEC('DBCC IND(testdb, textdata, -1)');
GO
SELECT PageFID, PagePID, ObjectID, IAM_chain_type, PageType
FROM sp_tablepages;

邀月工作室 

 

存储在数据行的LOB数据

如果你存储LOB数据类型值在常规数据页外,SQL Server每次在访问数据时必须执行额外的页读取,这与行溢出页非常类似。有时候,你可以将LOB数据存储在数据行以提高性能。表设置为"text in row"为ON即可。

例如下列命令将小于500字节的LOB数据存储在Employee表的常规数据行中:

EXEC sp_tableoption employee, 'text in row', 500; 

 注意:如果是Ntext型将只能是250字节。如果用ON代替500,默认值为256。禁用该选项,可以用OFF或0。

察看当前选项状态,可以通过目录视图sys.tables:

SELECT name, text_in_row_limit
FROM sys.tables
WHERE name = 'employee';

--name    text_in_row_limit
--
Employee    0 

我们再看一个例子:

CREATE TABLE HasText  
(
Col1 
char(3)       NOT NULL,
Col2 
varchar(5)    NOT NULL,
Col3 
text          NOT NULL,
Col4 
varchar(20)   NOT NULL
);
INSERT HasText VALUES  
    (
'AAA''BBB'REPLICATE('X'250), 'CCC');

 

 邀月工作室

 邀月工作室

 

我们启用行内文本数据,上限为500字节。

EXEC sp_tableoption HasText, 'text in row'500

启用选项后并没有强制文本数据移动到行内。我们必须强制移动数据:

UPDATE HasText  
SET col3 =  REPLICATE('Z'250);

 

此时,两个LOB页变成一个,并且数据移到行内:
邀月工作室
很多人会问另一个问题:如果我此时将text in row的最大长度设为一个较小的值,比如50,会如何?

EXEC sp_tableoption HasText, 'text in row'50;

正如大多数人预料的那样,此时,SQL Server自动增加一个LOB页。

 注意:

1、一个根结构至少24字节,所以,行内数据最小为24字节,所以下面的命令EXEC sp_tableoption HasText, 'text in row', 20;并不会执行。

2、变量列的增长优于行内的LOB数据存储。

3、SQL Server记录所有LOB数据的移动,因此,对一个大表来说,减少行内数据限制或OFF时将会消耗较长的时间。

 在很多情况下,我们不得不使用readtext.writetext和updatetext等重新排序,此时需要处理偏移和数据长度值。SQL Server 2005以上,你不得不决定是否需要限定列为8000字节或用其他方式处理大数据列和短列。SQL Ser2005/2008提供了很好的解决文案。我们后面会提到。

posted @ 2010-05-03 23:44  邀月  阅读(1642)  评论(4编辑  收藏  举报