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