:::: MENU ::::

Table creation failed due to row size limit

  • Feb 29 / 2012
  • 0
Data Modeling and Database Design, Data Types, dbDigger, DDL, SQL Server Error messages

Table creation failed due to row size limit

Following error messages may be generated while trying to create index on table having row size greater than 8060 bytes. In case where index is also tried to create following error would be generated
Index ‘%’ row length exceeds the maximum permissible length of ‘8060’ bytes.

And while trying to create table with out index and size greater than 8060 bytes, following error would be generated
Creating or altering table ‘%’ failed because the minimum row size would be 16011, including 7 bytes of internal overhead. This exceeds the maximum allowable table row size of 8060 bytes.

SQL Server extent size is 8060 bytes and is comprised of 8 pages. Individual table row under 8060 bytes size is requirement of table creation. However data types like image or text would not be considered as participant in max size. If row size is expected to cross this limit then error message would be generated and creation would be failed.

Work Around

Some work around may be applied to solve the issue.

  • Try to cut down the number of columns by normalization
  • Try to optimize the data types
  • Study feasibility to change columns like char(8000) columns with text data type

Following are some scenarios where error messages related to row max size may be generated

-- Row size error message without index   
 IF EXISTS (SELECT name FROM sys.objects WHERE name = 'tblMaxRowDemo')  
 DROP TABLE tblMaxRowDemo  
 GO  
 CREATE TABLE tblMaxRowDemo  
 (ID INT IDENTITY(1,1),  
 col1 CHAR(6000),col2 CHAR(6000))  
 GO  
 -- Row size error message with index   
 IF EXISTS (SELECT name FROM sys.objects WHERE name = 'tblMaxRowDemo')  
 DROP TABLE tblMaxRowDemo  
 GO  
 CREATE TABLE tblMaxRowDemo  
 (ID INT PRIMARY KEY IDENTITY(1,1),  
 col1 CHAR(6000),col2 CHAR(6000))  
 GO  
Consult us to explore the Databases. Contact us