SQL Server 2005 - 达到表行大小限制

问题描述：

在向表添加新列之前确定表的行大小并且不超过 8060 字节限制，是否有一种干净的方法?

Is there a clean way to determine the row size of a table before adding a new column to it and not go over the 8060 byte limit?

例如，如果表行长度当前为 8055 字节，而我想添加一个日期时间(8 字节)，这将超过 8063 字节(不包括空映射).

For example, if the table row length is currently 8055 bytes, and I want to add a datetime (8 bytes) this will go over as it will go to 8063 bytes (excluding the null mapping).

但是，如果我添加一个整数(4 个字节)，这将给它 8059 个字节，它适合表大小(不包括空映射).

However, if I add an integer (4 bytes), this will give it 8059 bytes, which fits inside the table size (excluding the null mapping).

我目前能够获取表的大小，但是我发现很难创建一个 SQL 函数来获取数据类型(整数、日期时间等)的大小以及该数据类型是否需要额外的字节来进行空映射.

I am currently able to get the size of the table, however I find it difficult to create an SQL functon to get the size of a datatype (int, datetime etc) and whether the datatype needs an extra byte for the null mapping.

是否有我可以使用/开发的函数，它接受两个变量:表名和数据类型:fnIsEnoughStorage('table1',int) 并从标量函数返回一个布尔值(是/否).

Is there a function I can use/develop that accepts two variables: tablename and datatype: fnIsEnoughStorage('table1',int) and returns a boolean (yes/no) from a scalar function.

如果为真，我将在测试确定后继续执行 ALTER TABLE 命令.

if true, i will proceed with the ALTER TABLE command after the test is determined.

答

此处的此查询将确定哪些表具有潜在危险，并且其最大行大小可能会超过可用的 8060 字节:

This query here will determine which tables are potentitally dangerous and have a possible maximum row size that would exceed the 8060 bytes available:

;WITH TableRowSizes AS
(
    SELECT 
        t.NAME 'TableName',
        COUNT(1) 'NumberOfColumns',
        SUM (c.max_length) 'MaxRowLength'
    FROM   
        sys.columns c
    INNER JOIN 
        sys.tables t ON c.object_id = t.object_id
    WHERE
        c.user_type_id NOT IN (98, 165, 167, 231)  -- sql_variant, varbinary, varchar, nvarchar
    GROUP BY 
        t.name
)
SELECT *
FROM TableRowSizes
WHERE MaxRowLength > 8060
ORDER BY MaxRowLength DESC

这并不意味着您的行实际使用超过 8060 个字节 - 它只是总结了每列可能的最大大小.

This doesn't mean your rows are actually using more than 8060 bytes - it just sums up the possible maximum size for each column.

如果您想确定当前实际使用的大小，您可能可以通过检查 DATALENGTH(colname) 函数来做类似的事情(而不是使用 sys.columns 中的理论最大值)

If you want to determine the actual currently used size, you probably can do something similar by examining the DATALENGTH(colname) function (instead of using the theoretical max value from sys.columns)

更新:根据 gbn 的响应在我的 CTE SELECT 中添加了 WHERE 子句 - 这些类型不应用于确定行是否可能突破 8060 字节大小限制.

Update: added a WHERE clause to my CTE SELECT based on gbn's response - those types should not be used in determining if the row potentially breaks the 8060 bytes size limit.

SQL Server 2005 - 达到表行大小限制

相关推荐