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)
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.
-
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
-
-
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
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)
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
try this:
;WITH CTE as(select *,LEN(ISNULL(col1,''))+LEN(ISNULL(col2,'')) as row_len from yourtable)
select * from CTE where row_len > 8060
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 thealloc_unit_type_desc
isROW_OVERFLOW_DATA
– dash Oct 5 '12 at 10:56