Home Forums SQL Server 2008 SQL Server 2008 - General Cannot create a row of size XXXX which is greater than the allowable maximum row size of 8060 RE: Cannot create a row of size XXXX which is greater than the allowable maximum row size of 8060

  • >> SQL Server use blocks of data named page. Each page can store 8kb, and include header and metadata. Therefore, It allows only 8060 bytes of data max to be stored in a row. Hence your row size will alwayas be <= 8060. It will never cross this.

    >> some column type can store the data outside the main row's page (like NVARCHAR(MAX))

    * you can get more information where physically the data is stored, using: DBCC IND ('<Database name>', '<table name>', 1);

    >> using external data like NVARCHAR(MAX) you still need to store the pointers, in the main page.

    http://improve.dk/what-is-the-size-of-the-lob-pointer-for-max-types-like-varchar-varbinary-etc/

    >> dont forget the overhead. Each row has headers.

    >> compression is somthing that take part in this game. A table cannot be enabled for compression when the maximum row size plus the compression overhead exceeds the maximum row size of 8060 bytes. For example, a table that has the columns c1 char(8000) and c2 char(53) cannot be compressed because of the additional compression overhead.

    >> The record-size limit for tables that use sparse columns is 8,018 bytes! Sparse columns are ordinary columns that have an optimized storage for null values. Sparse columns reduce the space requirements for null values at the cost of more overhead to retrieve nonnull values.

    >> without any change in a regular situation, you can use about 307 more or less, NVARCHAR(MAX) columns in a table, without seen this alert:

    Warning: The table "Temp" 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.

    with all this said, This is only an alert since the Server assume that if you have several dynamic suzes (like several NVARCHAR) then you might not use them all or fill them with long data (therefore you might not need any pointer, and the data will fit to the max row size). Trying to insert the data might raise an error if it is not fit to the max row size.

    I hope this is useful Smile

    Senior consultant and architect, data platform and application development, Microsoft MVP.