CODEWITHSUNDEEP
sql-server
Ming Hieu
Ming Hieu

Reputation: 301

Calculate table size to determinate exceeds allowed maximum of 8060 bytes

When I create a new table with so many columns with nvarchar type

create table testcolumnsize
(
a001    nvarchar(4000),
a002    nvarchar(4000),
--.....
a400    nvarchar(4000),
)

I received a warning message that told

Warning: The table "testcolumnsize" has been created, but its maximum row size exceeds the allowed maximum of 8060 bytes. INSERT or UPDATE to this table will fail if the resulting row exceeds the size limit

How can I calculate the created table's size according to it's columns data type to determinate whether it's size exceeds the allowed maximum of 8060 bytes

For example 

create table testcolumnsize
(
cint int,
cbigint bigint,
cfloat float,
cdatetime datetime,
--.....
)

Upvotes: 0

Views: 442

Answers (1)

Filippo Possenti
Filippo Possenti

Reputation: 1410

According to links I found, it's possible through a query.

If you're interested in individual rows, then one link provides the following code:

-- Declaring variables   
declare @table nvarchar(128);  
declare @idcol nvarchar(128);  
declare @sql nvarchar(max);  

--initialize those two values  
set @table = '[Person].[AddressType]'  
set @idcol = 'AddressTypeID, Name'  
set @sql = 'select ' + @idcol + ' , (0'  

-- This select statement collects all columns of a table and calculate datalength  
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'  

-- Execute sql query   
exec (@sql)

The core of the functionality appears to revolve around the datalength function, which appears to return the size in bytes that a certain column uses for a given row. This should give you the ability to detect whether a certain row exceeds the 8060 bytes limit.

The same can apparently be accomplished using sys.dm_db_index_physical_stats.

There are proponents also of the INFORMATION_SCHEMA.COLUMNS and its CHARACTER_MAXIMUM_LENGTH and CHARACTER_OCTET_LENGTH columns if you want to calculate the size of a "prospect" row.

See the following links for reference:

Upvotes: 1

Related Questions