27

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)

|improve this question

migrated from stackoverflow.com Oct 6 '12 at 6:56

This question came from our site for professional and enthusiast programmers.

38

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(' + 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.

|improve this answer
  • yeah its not applies for varchar I agree.Here above your query is not covering all the columns of a table – AnandPhadke Oct 5 '12 at 11:41
  • @AnandPhadke What columns is not covering? Thanks – Jaime Oct 5 '12 at 12:42
  • Why add one byte for a null column? Is that not zero bytes? Or is it stored internally as #0? – Paul Oct 20 '14 at 18:39
  • 2
    @Paul, it's zero bytes for variable length columns (varchar, nvarchar...), but it is the actual data type length for fixed length columns (int, smallint...), so 1 is kind of an estimation. NULLs are a whole Universe :) (there is also a NULL bitmap mask used to flag NULL values, that takes some space). stackoverflow.com/questions/4546273/… – Jaime Oct 21 '14 at 9:01
  • @Paul it will be stored as zero bytes if SQL Server is using any Data Compression. – d.popov Jan 25 '16 at 13:13
5

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)
|improve this answer
2

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
|improve this answer
0

try this:

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.