Max Row Size in SQL Server 2012 with Varchar(Max) Fields

SQL Server Maximum Row size Vs Varchar(Max) size

In Microsoft SQL Server, data (which includes indexes) are stored in one or more 8k (8192 bytes) "pages". There are different types of pages that can be used to handle various situations (e.g. Data, LOB, Index, AllocationMap, etc) . Each page has a header which is meta-data about that page and what it contains.

Most data is stored in the row itself, and one or more of these rows are in turn stored in a page for "in-row data". Due to the space taken by the row header, the largest a row can be (for "in-row" data) is 8060 bytes.

However, not all data is stored in the row. For certain datatypes, the data can actually be stored on a "LOB data" page while a pointer is left in the "in-row" data:

Legacy / deprecated LOB types that nobody should be using anymore (TEXT, NTEXT, and IMAGE), by default, always store their data on LOB pages and always use a 16-byte pointer to that LOB page.
The newer LOB types (VARCHAR(MAX), NVARCHAR(MAX), VARBINARY(MAX), and XML), by default, will attempt to fit the data directly in the row if it will fit. Else it will store the data on LOB pages and use a pointer of 24 - 72 bytes (depending on the size of the LOB data).

This is how you could store up to 78 GB + 4 bytes (can't forget about the INT Primary Key ;-) in a single row: the max row size will be between 940 bytes ((39 * 24) + 4) and 2812 bytes ((39 * 72) + 4). But again, that is just the maximum range; if the data in each of the 39 VARCHAR(MAX) fields is just 10 bytes, then all of the data will be stored in-row and the row size will be 394 bytes ((39 * 10) + 4).

Given that you have so many variable-length fields (whether they are MAX or not), the only way to estimate the size of future rows is to have a good idea about what data you will be storing in this table. Although, a table with all, or even mostly, MAX datatypes implies that nobody really has any idea what is going to be stored in this table.

Along those lines, it should be pointed out that this is a horribly modeled table / horrible use of MAX datatype fields, and should be refactored.

For more details about how data pages are structured, please see my answer to the following DBA.StackExchange question:

SUM of DATALENGTHs not matching table size from sys.allocation_units

Is there a way around the 8k row length limit in SQL Server?

If you are using SQL Server 2005, 2008 or 2012, you should be able to use NVARCHAR(max) or NTEXT which would be larger than 8,000 characters. MAX will give you 2^31 - 1 characters:

http://msdn.microsoft.com/en-us/library/ms186939(v=sql.90).aspx

Work around SQL Server maximum columns limit 1024 and 8kb record size

SQL Server Maximum Columns Limit

Bytes per short string column 8,000

Bytes per GROUP BY, ORDER BY 8,060

Bytes per row 8,060

Columns per index key 16

Columns per foreign key 16

Columns per primary key 16

Columns per nonwide table 1,024

Columns per wide table 30,000

Columns per SELECT statement 4,096

Columns per INSERT statement 4096

Columns per UPDATE statement (Wide Tables) 4096

When you combine varchar, nvarchar, varbinary, sql_variant, or CLR user-defined type columns that exceed 8,060 bytes per row, consider the following:

Surpassing the 8,060-byte row-size limit might affect performance because SQL Server still maintains a limit of 8 KB per page. When a combination of varchar, nvarchar, varbinary, sql_variant, or CLR user-defined type columns exceeds this limit, the SQL Server Database Engine moves the record column with the largest width to another page in the ROW_OVERFLOW_DATA allocation unit, while maintaining a 24-byte pointer on the original page. Moving large records to another page occurs dynamically as records are lengthened based on update operations. Update operations that shorten records may cause records to be moved back to the original page in the IN_ROW_DATA allocation unit. Also, querying and performing other select operations, such as sorts or joins on large records that contain row-overflow data slows processing time, because these records are processed synchronously instead of asynchronously.

Therefore, when you design a table with multiple varchar, nvarchar, varbinary, sql_variant, or CLR user-defined type columns, consider the percentage of rows that are likely to flow over and the frequency with which this overflow data is likely to be queried. If there are likely to be frequent queries on many rows of row-overflow data, consider normalizing the table so that some columns are moved to another table. This can then be queried in an asynchronous JOIN operation.

The length of individual columns must still fall within the limit of
8,000 bytes for varchar, nvarchar, varbinary, sql_variant, and CLR
user-defined type columns. Only their combined lengths can exceed the
8,060-byte row limit of a table.
The sum of other data type columns, including char and nchar data,
must fall within the 8,060-byte row limit. Large object data is also
exempt from the 8,060-byte row limit.
The index key of a clustered index cannot contain varchar columns
that have existing data in the ROW_OVERFLOW_DATA allocation unit. If
a clustered index is created on a varchar column and the existing
data is in the IN_ROW_DATA allocation unit, subsequent insert or
update actions on the column that would push the data off-row will
fail. For more information about allocation units, see Table and
Index Organization.
You can include columns that contain row-overflow data as key or
nonkey columns of a nonclustered index.
The record-size limit for tables that use sparse columns is 8,018
bytes. When the converted data plus existing record data exceeds
8,018 bytes, MSSQLSERVER ERROR 576 is returned. When columns are
converted between sparse and nonsparse types, Database Engine keeps a
copy of the current record data. This temporarily doubles the storage
that is required for the record. .
To obtain information about tables or indexes that might contain
row-overflow data, use the sys.dm_db_index_physical_stats dynamic
management function.

Creating table with n number of columns and datatype Nvarchar

CREATE Proc [dbo].[CreateMaxColTable_Nvarchar500]
(@TableName nvarchar(100),@NumofCols int)
AS
BEGIN

DECLARE @i INT
DECLARE @MAX INT
DECLARE @SQL VARCHAR(MAX)
DECLARE @j VARCHAR(10)
DECLARE @len int
SELECT @i=1
SELECT @MAX=@NumofCols
SET @SQL='CREATE TABLE ' + @TableName + '('

WHILE @i<=@MAX

BEGIN
select @j= cast(@i as varchar)
SELECT @SQL= @SQL+'X'+@j  +' NVARCHAR(500) , '
SET @i = @i + 1
END
select @len=len(@SQL)

select  @SQL = substring(@SQL,0,@len-1)

SELECT @SQL= @SQL+ ' )'

exec (@SQL)

END

For more information you can visit these links:

http://msdn.microsoft.com/en-us/library/ms186981%28SQL.105%29.aspx?PHPSESSID=tn8k5p1s508cop8gr43e1f34d2

http://technet.microsoft.com/en-us/library/ms143432.aspx

But please could you tell the scenario why do you need a table with so much columns?
I think you should consider about the re-design of the database.

SQL Server 2012 - Separate 2 Varchar(max) columns to separate table?

If stored in a separate table, rows would be truncated to only include previous 6 months, reducing table size of first table.

The rows would have to be deleted, not truncated, and then the BLOB space would have to be reclaimed by running ALTER INDEX ... REORGANIZE WITH (LOB_COMPACTION = ON)

If you'd store instead the blobs in the original table, you would have to update the rows to SET blob = NULL and then reclaim the space with ALTER INDEX ... REORGANIZE WITH (LOB_COMPACTION = ON)

So when it boils down to the details, you aren't achieving much using a split table, imho. So I stick to my earlier advice from SQL Server varbinary(max) and varchar(max) data in a separate table: I see no benefits in split, but I see trouble from having to maintain the rows consistent 1:1 between the splits.

You may have a case if you split and partition the 'blobs' table. Then you could, indeed, deallocate very efficiently the old space by switching 'out' the old partition and replacing them with an empty one, and then dropping the switched out data. That is something to consider. Of course, you code would have to be smart enough when it joins the two 'splits' to consider that the blobs may be gone (ee. use OUTER JOIN).

Find length of varchar(max) column

Yes, the maximum length of a varchar(max) or nvarchar(max) column is not unlimited.
The storage limit is 2GB (2^31 -1 bytes).
It behaves as a regular varchar/nvarchar column when the data is less than 8kb. Then when the data for a individual row is greater than 8kb, it is stored as a large-object text/ntext value. You can think of it like a wrapper for the 2 data types.

However, the maximum character length is not 2000000000.
It is 2147483645 for a varchar(max) column and 1073741822 for a nvarchar(max) column.
This is because an nvarchar has 2 bytes per character and a varchar has 1 byte per character and 2 bytes are needed to store the data length of a variable column.

I don't think any extra metadata is subtracted from the 2GB limit but I will comment tomorrow if I find anything else.

Retrieve the maximum length of a VARCHAR column in SQL Server

Use the built-in functions for length and max on the description column:

SELECT MAX(LEN(DESC)) FROM table_name;

Note that if your table is very large, there can be performance issues.