Microsoft SQL Server 2012 Internals

Large object storage

SQL Server 2012 has two special formats for storing data that doesn’t fit on the regular 8 KB data page. These formats allow you to store rows that exceed the maximum row size of 8,060 bytes. As discussed in Chapter 6, this maximum row size value includes several bytes of overhead stored with the row on the physical pages, so the total size of all the table’s defined columns must be slightly less than this amount. In fact, the error message that you get if you try to create a table with more bytes than the allowable maximum is very specific. If you execute the following CREATE TABLE statement with column definitions that add up to exactly 8,060 bytes, you’ll get the error message shown here:

USE testdb; GO CREATE TABLE dbo.bigrows_fixed ...