How can I get the actual data size per row in a SQL Server table?

Question

I found this script sql-server-2005-reaching-table-row-size-limit that seems to return the row size per defined data type lengths. I need a script that would give me all the rows in a table that their max data size is over the recommended 8024 (whatever MS recommends)

Answer 1

Try this script:

declare @table nvarchar(128)
declare @idcol nvarchar(128)
declare @sql nvarchar(max)
--initialize those two values
set @table = 'YourTable'
set @idcol = 'some id to recognize the row'
set @sql = 'select ' + @idcol +' , (0'
select @sql = @sql + ' + isnull(datalength(' + QUOTENAME(name) + '), 1)' 
        from  sys.columns 
        where object_id = object_id(@table)
        and   is_computed = 0
set @sql = @sql + ') as rowsize from ' + @table + ' order by rowsize desc'
PRINT @sql
exec (@sql)

The rows will be ordered by size, so you can check from top to down. The rowsize column is in bytes. See more info: DATALENGTH()

Answer 2

I liked the above from Jaime. I added some square brackets to handle weird column names.

    declare @table nvarchar(128)
    declare @idcol nvarchar(128)
    declare @sql nvarchar(max)
--initialize those two values
set @table = 'YourTable'
set @idcol = 'some id to recognize the row'

set @sql = 'select ' + @idcol +' , (0'

select @sql = @sql + ' + isnull(datalength([' + name + ']), 1)' 
        from sys.columns where object_id = object_id(@table)
set @sql = @sql + ') as rowsize from ' + @table + ' order by rowsize         desc'

PRINT @sql

exec (@sql)

Answer 3

And I liked the above from Speedcat and extend it to list all Tables with rowcounts and total bytes.

declare @table nvarchar(128)
declare @sql nvarchar(max)
set @sql = ''
DECLARE tableCursor CURSOR FOR  
SELECT name from sys.tables
open tableCursor
fetch next from tableCursor into @table
CREATE TABLE #TempTable( Tablename nvarchar(max), Bytes int, RowCnt int)
WHILE @@FETCH_STATUS = 0

begin
    set @sql = 'insert into #TempTable (Tablename, Bytes, RowCnt) '
    set @sql = @sql + 'select '''+@table+''' "Table", sum(t.rowsize) "Bytes", count(*) "RowCnt" from (select (0'
select @sql = @sql + ' + isnull(datalength([' + name + ']), 1) ' 
    from sys.columns where object_id = object_id(@table)
set @sql = @sql + ') as rowsize from ' + @table + ' ) t '
exec (@sql)
FETCH NEXT FROM tableCursor INTO @table  

end
PRINT @sql
CLOSE tableCursor

DEALLOCATE tableCursor
select * from #TempTable
select sum(bytes) "Sum" from #TempTable
DROP TABLE #TempTable

Answer 4

try this:

;WITH CTE as(select *,LEN(ISNULL(col1,''))+LEN(ISNULL(col2,'')) as row_len from yourtable)
select * from CTE where row_len > 8060

Answer 5

Here is a version of Romulus answer but that should work with schemas:

declare @table nvarchar(128)
declare @schema nvarchar(128)
declare @sql nvarchar(max)
SET @sql = ''
DECLARE tableCursor CURSOR FOR  
SELECT TABLE_NAME, TABLE_SCHEMA from INFORMATION_SCHEMA.TABLES
OPEN tableCursor
FETCH NEXT FROM tableCursor INTO @table, @schema
CREATE TABLE #TempTable( Tablename nvarchar(max), Schemaname nvarchar(max), Bytes bigint, RowCnt bigint)
WHILE @@FETCH_STATUS = 0

BEGIN
    SET @sql = 'INSERT INTO #TempTable (Tablename, Schemaname, Bytes, RowCnt) '
    SET @sql = @sql + 'SELECT '''+@table+''' "Table",'''+@schema+''' "Schema", sum(t.rowsize) "Bytes", count(*) "RowCnt" FROM (SELECT (0'
SELECT @sql = @sql + ' + ISNULL(DATALENGTH([' + name + ']), 1) '
    FROM sys.columns WHERE object_id = object_id(@schema + '.' + @table)
SET @sql = @sql + ') AS RowSize FROM ' + @schema + '.' + @table + ' ) t'
EXEC (@sql)
FETCH NEXT FROM tableCursor INTO @table, @schema

END
CLOSE tableCursor

DEALLOCATE tableCursor
SELECT Tablename "Table", Schemaname "Schema", Bytes, RowCnt "Rows", Bytes / RowCnt "Average"
    FROM #TempTable WHERE RowCnt > 0 ORDER BY Average DESC
DROP TABLE #TempTable

Answer 6

I know this is an old question, but there's a better way:

select
    SomeOtherColumn,
    Bytes = datalength((select x.* from (values(null))data(bar) for xml auto))
from Table x