Geeks With Blogs

Arthur Zubarev Compudicted

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)

Posted on Friday, October 3, 2014 11:55 AM | Back to top


Comments on this post: How to get maximum row size of a SQL Server table

# re: How to get maximum row size of a SQL Server table
Requesting Gravatar...
On line 13 it is average not everage!
Left by TATWORTH on Oct 05, 2014 11:52 AM

# re: How to get maximum row size of a SQL Server table
Requesting Gravatar...
Of course TATWORTH it is my bad, thank you for catching the typo up!
Left by Arthur on Oct 07, 2014 9:58 PM

Your comment:
 (will show your gravatar)


Copyright © Compudicted | Powered by: GeeksWithBlogs.net