I am doing a lot of ETL work (typically) and one of the particulars I want to know planning my packages design is the maximum length of a table row.
Just to expand further, it is often prudent to know the row size for future performance or for capacity planning.
So, without further ado, here is the SQL code (works on SQL Server 2005 and onward):
1: DECLARE @table_name NVARCHAR(115),
2: @1stCol NVARCHAR(115),
3: @sql NVARCHAR(MAX);
4:
5: -- Initialize the table name to sample
6: SET @table_name = 'THE TABLE NAME';
7:
8: SELECT TOP 1
9: @1stCol = name
10: FROM sys.columns
11: WHERE object_id = OBJECT_ID(@table_name);
12:
13: -- If you need the total rows for say an eaverage then drop the TOP N clause
14: SET @sql = 'SELECT TOP 1 ' + @1stCol + ', ROW_NUMBER() OVER (ORDER BY ' + @1stCol + ') AS [Record Number]' + ' , (0';
15:
16: SELECT
17: @sql = @sql + ' + ISNULL(DATALENGTH(' + name + '), 1)'
18: FROM sys.columns
19: WHERE object_id = OBJECT_ID(@table_name)
20: SET @sql = @sql + ') AS [Row Size in Bytes] FROM ' + @table_name + ' ORDER BY [Row Size in Bytes] DESC';
21:
22: -- Optionally, print the statement
23: PRINT @sql
24:
25: -- Execute
26: EXEC (@sql)
Using my code you can find the average row size or can calculate the total table size (e.g. using Excel)