A customer I work with questioned me today in regards to hearing that in Sql 2005 you can now have rows that exceed 8060kb in size, the limit of a single row in Sql 2000 (not counting text/blob data). Here’s some information on how this works in 2005…
In Sql 2000, if you try to create a table that has a possible total of data storage > 8060 byes, you’ll see a warning like the following (use the sample create table statement to repro on your own instance if you like):
create table zTest (id int not null, char1 varchar(8000) not null, char2 varchar(8000) not null)
Warning: The table ‘zTest’ has been created but its maximum row size (16029) 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 table is successfully created, but if you try running a statement that causes data in a single record to exceed the 8060 byte limit, you’ll get an error like the following:
insert zTest (id, char1, char2) select 1, replicate(‘a’,8000), replicate(‘b’,8000)
Msg 511, Level 16, State 0, Line 1
Cannot create a row of size 16017 which is greater than the allowable maximum of 8060.
The statement has been terminated.
In Sql 2005, this is no longer a problem for VARIABLE length column data that exceeds the 8060 byte threshold – you still have a maximum FIXED row size of 8060 bytes. To demonstrate, try running the following statement, which tries to create a table using fixed-length columns only, on a Sql 2005 instance of your own and you will still receive an error:
create table zTest (id int not null, char1 char(8000) not null, char2 char(8000) not null)
Msg 1701, Level 16, State 1, Line 1
Creating or altering table ‘zTest’ 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.
However, if you try to run the original create table statement from the Sql 2000 test above, not only do you not receive a warning on creation of the object:
create table zTest (id int not null, char1 varchar(8000) not null, char2 varchar(8000) not null)
Command(s) completed successfully.
but, you also do not receive an error when you proceed to insert data into the table in exces of 8060 bytes:
insert zTest (id, char1, char2) select 1, replicate(‘a’,8000), replicate(‘b’,8000)
(1 row(s) affected)
Clearly, with Sql 2005 you can now store rows that exceed 8060 bytes in size using variable length data types.
This is made possible in Sql 2005 thanks to a new storage methodology called ‘Row-overflow data’. With Sql 2000, a single row’s data HAS to fit on a single page, however in Sql 2005, and variable length data in a single row that exceeds the available page-space is pushed into a seperate page, called a row-overflow page. A pointer is left in the original page of In-row data for the record pointing to the row-overflow page(s) that contain the given column’s data for the row. When a request is made to fetch the data for the given row, the engine reads through the in-row data as normal, recognizes the pointer to the overflow data, jumps to the Row-overflow page(s) to fetch the data, then returns to continue normal operations. See my blog post titled “How can I get sysindexes information in Sql 2005?” for some information on how to retrieve meta-data information in regards to this row-overflow data for given objects.
