Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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)

share|improve this question

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

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

    
You can try using msdn.microsoft.com/en-us/library/ms188917%28v=sql.105%29.aspx - SELECT * FROM sys.dm_db_index_physical_stats(DB_ID(N'Database_Name'), OBJECT_ID(N'Table_Name'), NULL, NULL, 'DETAILED') and look for anything where the alloc_unit_type_desc is ROW_OVERFLOW_DATA –  dash Oct 5 '12 at 10:56
    
MS SQL server allows only 8060bytes of data max to be stored in a row.Hence your row size will alwayas be <= 8060.It will never cross this. –  AnandPhadke Oct 5 '12 at 11:01
1  
@AnandPhadke This is not totally right: msdn.microsoft.com/en-us/library/ms186981%28SQL.90%29.aspx –  Jaime Oct 5 '12 at 11:24

2 Answers 2

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)
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.

share|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
1  
Works wonders for me, thanks! –  Bondt Oct 13 at 10:08
    
Why add one byte for a null column? Is that not zero bytes? Or is it stored internally as #0? –  Paul Oct 20 at 18:39
1  
@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 2 days ago

try this:

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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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