We want to calculate the total "on row" storage bytes for each row in the table. As we understand it, we must add up the DATALENGTH() of each column while also accounting for NULLs and things like VARCHAR(MAX) which only have a 24-byte pointer "on row". We are aware there is also some overhead for each row which is not accounted for in the query below.
SELECT ROW_ID,
CASE
WHEN COLUMNPROPERTY(OBJECT_ID('EXAMPLE_TABLE'),'COL1','PRECISION') = -1 THEN 24
ELSE ISNULL(DATALENGTH(COL1), 1)
END
+
CASE
WHEN COLUMNPROPERTY(OBJECT_ID('EXAMPLE_TABLE'),'COL2','PRECISION') = -1 THEN 24
ELSE ISNULL(DATALENGTH(COL2), 1)
END
+
CASE
WHEN COLUMNPROPERTY(OBJECT_ID('EXAMPLE_TABLE'),'COL3','PRECISION') = -1 THEN 24
ELSE ISNULL(DATALENGTH(COL3), 1)
END
+
...
...
AS ROW_SIZE
FROM EXAMPLE_TABLE
ORDER BY ROW_SIZE DESC
;
What a beast! And it's only an approximation.
Then we discovered
DBCC SHOWCONTIG ('EXAMPLE_TABLE') WITH TABLERESULTS
which returns MaximumRecordSize. This reveals that there is already an algorithm buried somewhere within SQL Server which is capable of calculating the exact size of a row.
How can we access that algorithm directly?
Best Answer
In so far as the answer to this question, specifically, you can't access it directly. There is nothing where you can say
SELECT GetMeMaxRowSize(MyTable, MyPartition, MyIndex)
.However, as LowlyDBA has pointed out, you can use sys.dm_db_index_physical_stats to give you more information than ShowContig. The quite interesting thing is that if you run
DBCC SHOWCONTIG()
, capturing deprecated information, you should see a message to use the aforementioned DMV in place of theShowContig
command.