In some cases, if a large variable-length column shrinks, SQL Server moves
it into the regular row but doesn't bother checking whether the updated row
will fit on the data page if the column isn't being reduced by more than 1000
bytes in length.You can see that behavior with this next example,assuming you've
created the bigrows table and inserted only the one row in the earlier example
that has 2100 characters in each column. The UPDATE statement in Listing
5 reduces the size of the first column by 500 bytes, thereby reducing the
row size to 7900 bytes, which should all fit on the one data page.
However, if you check the DBCC IND output by using the code in Listing
6, you'll see that a row-overflow?type page still exists. Now run the UPDATE
statement in Listing 7 to reduce the size
of the first column by more than 1000 bytes, and check the DBCC IND output again.You
should see only three rows of output now, since a rowoverflow data page no longer
exists. The IAM for the row-overflow data pages hasn't been removed, but you
should no longer have a page with pageType 3.
When a column is stored on a rowoverflow page, SQL Server has to store a pointer
to the row-overflow page in the regular data row, which includes the offset
value of the column data on the rowoverflow page. This pointer can take up to
24 bytes in the original row. For this reason, any variable-length columns of
less than 24 bytes will never be stored on row-overflow pages.
My Data Runneth Over
Row-overflow data storage applies only to columns of variable-length data that
don't exceed the normal variable-length maximum of 8000 bytes per column.Also,to
store a variable-length column on a row-overflow page, the following conditions
must be met:
- All the fixed-length columns, including overhead bytes, must add up to no
more than 8060 bytes (and the pointer to the row-overflow data adds 24 bytes
of overhead to the row).
- The actual length of the variable-length column must be more than 24 bytes.
If you have single columns that might need to store more than 8000 bytes, you
need to use either LOB (text,image,or ntext) columns or use the new SQL Server
2005 varchar (MAX) data type. I'll tell you about LOB data and varchar(MAX)
next month.
End of Article
Prev. page
1
[2]
next page -->
