代码改变世界

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

  潇湘隐者  阅读(2692)  评论(17编辑  收藏  举报

昨天一个同事突然问我,说他在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

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')

编辑推荐:
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· .NET Core 托管堆内存泄露/CPU异常的常见思路
· PostgreSQL 和 SQL Server 在统计信息维护中的关键差异
· C++代码改造为UTF-8编码问题的总结
· DeepSeek 解答了困扰我五年的技术问题
阅读排行:
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· 清华大学推出第四讲使用 DeepSeek + DeepResearch 让科研像聊天一样简单!
· 实操Deepseek接入个人知识库
· CSnakes vs Python.NET:高效嵌入与灵活互通的跨语言方案对比
· Plotly.NET 一个为 .NET 打造的强大开源交互式图表库
历史上的今天:
2010-05-19 (转)XML CDATA是什么?
点击右上角即可分享
微信分享提示