Problem solve Get help with specific problems with your technologies, process and projects.

A workaround to SQL Server's 8,060-byte row length limit

There are several possible workarounds to the 8,060-byte row length limit, and most of them involve a re-thinking of the table design. Serdar Yegulalp explains.

The total amount of space used to store an individual row of data in SQL Server cannot exceed 8,060 bytes.

The reason for the 8,060-byte limit is simple. SQL Server allocates and stores data in units called pages, and a single SQL Server data page limits the amount of data that can be stored to 8,060 bytes. This design doesn't allow rows to be broken across data pages, which not only slows down retrieval but makes it much more difficult to index databases.

You can demonstrate to yourself how this works with a quick experiment. Try creating a table named Table1 with three char(4000) columns. You'll be able to create the first two columns, but not the third. When you try to create the third, you'll get an error that reads:

Creation of table 'Table1' failed because the row size would be 12,021, including internal overhead. This exceeds the maximum allowable table row size, 8,060.

There are several possible workarounds, and most of them involve a re-thinking of the table design. If you need to have multiple char columns, consider placing them in another table and performing a JOIN operation to retrieve them and put them together. Another possibility is to use variant columns or text columns with proper covering indexes -- and have the text itself stored in another physical file, separate from the file used to hold the table itself, to keep things efficient.

If you're wondering why multiple variant / text columns can be placed in a row without blowing out the 8,060-byte limit, it's because variant / text columns are simply a pointer to the actual data, which is stored elsewhere. No matter how big the data is, the pointer only takes up a few bytes in the row where it's stored, so a row can contain many such pointers quite easily.

Serdar Yegulalp is the editor of the Windows 2000 Power Users Newsletter. Check out his Windows 2000 blog for his latest advice and musings on the world of Windows network administrators – please share your thoughts as well!

Dig Deeper on SQL Server Database Modeling and Design