Home > sql > Find Tables that can cover more than a SQL Server Page per ROW

Find Tables that can cover more than a SQL Server Page per ROW

I was looking around this morning for a way to see if a table has columns that whose max values would be larger than a 8K SQL Server Page causing 1 row of data to span multiple pages in SQL Server.  Within the database everything is managed using Pages which are 8192 bytes (8 X 1024) or 8KB.  I was playing with some of the catalog views within SQL Server 2008 and came up with the following script and wanted to share it with everyone.  The data you can hold on 1 SQL Server page is 8060 bytes (rest is used internally) so this script gets the MAX length of each column in a table, sums it up and displays a ‘Y’ or ‘N’ flag if that table has the possibility of one row spanning multiple pages:

    OBJECT_SCHEMA_NAME(tab.object_id,DB_ID()) AS SchemaName,
    tab.name AS TableName,
    part.rows as NumberRows,
    SUM(col.max_length)  as MaxRowLengthBytes,
    CASE    WHEN SUM(col.max_length) > 8060 THEN 'Y'
            WHEN SUM(col.max_length) <= 8060 THEN 'N'
        ELSE 'UNKNOWN'
    END AS LargerThanPageFlag
FROM sys.tables tab
    INNER JOIN sys.all_columns col
        ON tab.object_id = col.object_id
    INNER JOIN sys.types datatypes
        ON col.system_type_id = datatypes.system_type_id
        AND col.user_type_id = datatypes.user_type_id
    INNER JOIN sys.partitions part
        ON part.object_id = tab.object_id
        AND part.index_id IN (1,0) --Used to get row counts
WHERE tab.is_ms_shipped = 0
GROUP BY OBJECT_SCHEMA_NAME(tab.object_id,DB_ID()), tab.name, part.rows
ORDER BY MaxRowLengthBytes desc
Categories: sql
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: