Question: How can I calculate the sql row size?

 Answer: Calculating the  row-level row size is complicated and challenging.  One of the key problems is SQL Server defines space at the extent level .

 An extent is made up of 8 x 8 KB physically  contiguous pages .  The  complication for calculating a row size is : Even if one row exists – for example 4 bytes , an extent has to be allocated.

Read this post for information on the difference between uniform and mixed extents - Disk IO performance , disk block size tuning and SQL Servers

 There is complicated formula on the MSDN Online - http://msdn.microsoft.com/en-us/library/ms189124.aspx to predict the space required to store data in a heap

There are some reliable methods of estimating  row sizes . Using DBCC SHOWCONTIG  , sys.dm_db_index_physical_stats  and SysColumns.

Each method offers a slightly different perspective  on row size.

---METHOD 1  : DBCC SHOWCONTIG

dbcc showcontig ('TableName') with tableresults

--check max min and average record size



--METHOD 2 : sys.dm_db_index_physical_stats
--dbcc showcontig is to be deprecated 
SELECT * FROM sys.dm_db_index_physical_stats (DB_ID(N'DatabaseName'), OBJECT_ID(N'TableName'), NULL, NULL , 'DETAILED')


--METHOD 3 : SysColumns

use db_name
go
create table ##tmpRowSize (TableName varchar(100),RowSizeDefinition int)
exec sp_msforeachtable 'INSERT INTO ##tmpRowSize Select ''?'' As TableName, SUM(C.Length) as Length from dbo.SysColumns C where C.id = object_id(''?'') '
select * from ##tmpRowSize order by RowSizeDefinition  desc
drop table ##tmpRowSize

 Read More

SQL Server - How to find the largest sql index and table size

SQL Server - Calculate table size with existing data

Storage Checklist FAQ