Calculate table size to determinate exceeds allowed maximum of 8060 bytes

Question

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

Seems like a very bad idea to have 400 "a" columns. Have one "a" column, and 400 rows instead. — jarlh, Feb 18 '20 at 08:39
@a_horse_with_no_name: sorry, It's sql server, I using version 18.4 — Ming Hieu, Feb 18 '20 at 09:01
@jarlh it's just an example for my case: a table with many columns with many types. The 400 columns table with all nvarchar(4000) to emulator the case shows warning message above — Ming Hieu, Feb 18 '20 at 09:04
@MingHieu - it's possible that you're using SQL Server *Management Studio* 18.4 to *access* SQL Server. But 18.4 isn't the version of *SQL Server itself*. — Damien_The_Unbeliever, Feb 18 '20 at 09:04
I would simply go for Normalization if i have requirement of 400 columns just in one table. — Pankaj_Dwivedi, Feb 18 '20 at 09:25
Use `nvarchar(max)` and then its stored outside the row (if you have to design your table this way - it doesn't sound good on the face of it). — Dale K, Feb 18 '20 at 10:34

score 1 · Answer 1 · answered Feb 18 '20 at 09:19

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:

Calculate table size to determinate exceeds allowed maximum of 8060 bytes

1 Answers1