Freigeben über

Row Size limitation in SQL 2000 and 2005

  • Artikel

When you creating a table, you may encounter the following information: ' Table <table name> has been created but its maximum row size(11038) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE Of a row in this table will fail if the resulting row length exceeds 8060 bytes.

The reason for the error is that maximum size of a row on your table exceeds 8kb. This restriction is by SQL 2000 design. SQL Server engine stores it's data on 8kb pages & a single record must fit in a single page and cannot be spanned to multiple pages. You may want to reduce number of

fields on your table or shorten the field length of some columns.

Data pages are the structures that contain all data that is not text or image. As with all other types of pages in SQL Server, data pages have a fixed size of 8 KB, or 8192 bytes. They consist of three major components: the page header, data rows, and the row offset array. The following is the structure of a data page in SQL Server.

Structure of a data page

In SQL Server 7.0 and SQL Server 2000 we are allowed to create a table that contains variable columns with a total length GT. 8060 (the total sum of fixed length columns must be LT 8060 as well as the length of an individual column).

However when executing an inset or an update statement SQL Server may fail if the actual length is greater than 8K. You may want to try the following statements in SQL 2000 to see this behavior:

CREATE TABLE mytab ( col1 varchar(8000), col2 varchar(8000) )

-- Warning: The table 'mytab' has been created but its maximum row size (16025) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes.

INSERT INTO mytab ( col1, col2 )

SELECT REPLICATE ( 'A', 8000), REPLICATE ( 'A', 8000)

--Server: Msg 511, Level 16, State 1, Line 1

--Cannot create a row of size 16013 which is greater than the allowable maximum of 8060.

--The statement has been terminated.

INSERT INTO mytab ( col1, col2 )

SELECT REPLICATE ( 'A', 8000), REPLICATE ( 'A', 60)

--Server: Msg 511, Level 16, State 1, Line 1

--Cannot create a row of size 8073 which is greater than the allowable maximum of 8060.

--The statement has been terminated.

INSERT INTO mytab ( col1, col2 )

SELECT REPLICATE ( 'A', 8000), REPLICATE ( 'A', 47)

In SQL 2005 this execution will succeed

For example:

CREATE TABLE mytab ( col1 varchar(8000), col2 varchar(8000) )

--Command(s) completed successfully.

INSERT INTO mytab ( col1, col2 )

SELECT REPLICATE ( 'A', 8000), REPLICATE ( 'A', 8000)

--(1 row(s) affected)

INSERT INTO mytab ( col1, col2 )

SELECT REPLICATE ( 'A', 8000), REPLICATE ( 'A', 9000)

--(1 row(s) affected)

SELECT len(col1) as len1, len(col2) AS len2 FROM mytab

len1 len2

----------- -----------

8000 8000

8000 8000

(2 row(s) affected)

If both varchars columns contain 8000 bytes the insert will succeed in 2005 and it will fail in the previous releases.

The 8060 limit for individual varchar/varbinary columns is still in place. However, it will truncate the charters larger than 8000.

In SQL 2005, surpassing the 8,060 row size limit may impact performance. This is because SQL Server still maintains a limit of 8 KB per page. When a combination of varchar, nvarchar, varbinary, or sql_variant columns exceeds this limit, SQL Server moves the record column with the largest width to another page, while maintaining a 24-byte pointer on the original page.

Moving large records to another page happens dynamically as records are lengthened based on update operations. Update operations that shorten records may result in records moved back to the original page. In addition, querying and performing other select operations such as sorts or joins on large records that contain row-overflow data slows processing time.

To fetch a column values that has been pushed off the main row requires an extra I/O. Besides, having wide rows also reduces the scan density - because fewer rows fit on a page.

If you find the table has many rows with overflow data then you should seriously consider normalizing the table.

In SQL 2005, VarChar(MAX), NVarChar(MAX) and VarBinary(MAX) allow storage of data up to 2 gigabytes. Image/Text data type in SQL 2000/2005 is also stored separately and will not be covered in 8KB size.

Row-Overflow Data Exceeding 8 KB

https://msdn2.microsoft.com/en-us/ms186981.aspx

Maximum Capacity Specifications for SQL Server 2005

https://msdn2.microsoft.com/en-us/library/ms143432.aspx

Comments

  • Anonymous
    March 22, 2007
    On personal opinion, I find this very helpful. Guys, I have also posted some more relevant info further on this, not sure if you find it useful: http://www.bidmaxhost.com/forum/

  • Anonymous
    December 07, 2009
    very interesting. thanks for posing the info.

  • Anonymous
    December 07, 2009
    thats posting the info you idiot (was me)

  • Anonymous
    July 20, 2011
    I am getting same error i think it is not resolved in 2005

  • Anonymous
    June 05, 2012
    Thank you very much for this great information.

  • Anonymous
    July 21, 2013
    Hi, I was utilizing webservice and got same error. Author explain every thing beautifully. Thanks for such a nice post. Is there any wasy to remove this limitations. Manu Visit: www.kidsmitra.com