SQL SERVER 表最小行的一个纠结问题

昨天一个同事突然问我，说他在SQL 2000数据库创建如下表的时候，突然碰到了下面一条警告信息。SQL脚本和警告信息如下：

 IF OBJECT_ID(N'Log') IS  NULL

BEGIN
CREATE TABLE Log
(
    [Date]         DATETIME,
    [Thread]       NVARCHAR(255),
    [Level]        NVARCHAR(50),
    [Logger]       NVARCHAR(255),
    [Message]      NVARCHAR(4000),
    [Exception]    NVARCHAR(4000)
    
)
END
ELSE
  PRINT('该表已经存在，请检查数据库');
GO


Warning: The table 'Log' has been created but its maximum row size (17159) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes.

我以前也没有遇见过这样的警告信息，当时我在SQL SERVER 2000 下面执行这段脚本，果然有这个警告信息，还有就是为什么Maximun ROW SIZE 是17159？；当我在SQL SERVER 2005 下面执行这段脚本却没有警告信息出现，难道SQL SERVER 2005 与SQL SERVER 2000在存贮机制上面有什么不同？在搞清楚这些问题前，我们先来看看其它的一些相关问题，就是SQL SERVER 2000/2005中最大数据行都是8060字节（对定长数据而言）， 其中SQL SERVER 2000中可以使用的大小为8039字节， 而SQL SERVER 2005可以使用的大小为8053字节。我们可以从下面的脚本中实验一下（SQL SERVER 2005）

CREATE TABLE TEST
(
    FIELD1 CHAR(4000),
    FIELD2 CHAR(4000),
    FIELD3 CHAR(53)
)

CREATE TABLE TEST1
(
    FIELD1 CHAR(4000),
    FIELD2 CHAR(4000),
    FIELD3 CHAR(54)

） 

最小行大小8061 = 4000 + 4000 + 54 + 7（内部开销）。下面我们改变下上面脚本的数据类型，如下所示，看看在SQL SERVER 2005下的情况

IF OBJECT_ID(N'Log') IS  NULL
BEGIN
CREATE TABLE Log
(
    [Date]             DATETIME,
    [Thread]           CHAR(255),
    [Level]            CHAR(50),
    [Logger]           CHAR(255),
    [Message]          CHAR(4000),
    [Exception]        CHAR(4000)
    
)
END
ELSE
  PRINT('该表已经存在，请检查数据库');

GO

 

那么最小行8575是怎么算出来的呢，我们先看这张经典数据行结构图（引自Inside SQL SERVER）

 

 其实就是8 + 255 + 50 + 255 + 4000 + 4000 = 8568   + 7 = 8575  其中的7个字节是这样来的

Status Bits A                     1

Status Bits B                       1

Length of fixed-length ........               2 

number of columns 2 

Null bitmap 1 bit for each column         （6/8）1

由于表里面没有变长字段，所以其它与变长相关的字节为0 所以为7。 那么接下来我们看看开篇的问题为什么 maximum row size (17159)，

8 + 255* 2 + 50 * 2 + 255 * 2 + 4000 * 2 + 4000 *2   = 17128

1 + 1 + 2 + 2 + 1 + 2 + 2* 5  = 19 

那么17128 + 19 =  17147 但是结果是17159，有点不明，查了很多资料也没搞清楚，这个17159 是怎么算出来的，呵呵，希望高手来解答！

呵呵，感谢邀月老大的解答：本来我怎么也找不出的12个字节即是： Heap中非聚集索引的叶级页有一个索引键列值（本例中由系统自动添加),加上一实际数据行的RID,即4+8=12

SELECT t.name AS [Table Name], i.name AS [Index Name], i.minlen as minlen

FROM sysobjects AS t JOIN sysindexes AS i ON t.id = i.id

WHERE t.id > 100  AND t.id=object_id( 'log')