SQL Server 2005 - Reaching Table Row Size Limit

Question

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?

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

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

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.

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.

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

Answer 1

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

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

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)

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.