Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
I know that varchar(max) and nvarchar(max) allow upto 2GB of data to be held, but this is not relevant to this question.
A page is 8KB = 8192 Bytes
In varchar, 1 character is 1 byte. Whereas in nvarchar, 1 character is 2 bytes.
varchar is allowed to have 8000 characters (8000 Bytes). Whereas nvarchar is allowed to have 4000 characters (8000 Bytes).
Is this because the page size is 8192 Bytes?
Yes, as far as I am aware, that is the main reason one would use VARCHAR(8000) or NVARCHAR(4000) instead of any size bigger including MAX, because those are the largest sizes for each data type respectively that can store the entire data of that field in row meaning on the same data page.
This is because SQL Server uses 8 KB pages which is 8,192 bytes, where 8,000 of those bytes are provided for user data and the remaining 192 are reserved for overhead.
You can find more in depth reading on VARCHAR(8000) vs VARCHAR(MAX) and their differences in Comparing VARCHAR(max) vs VARCHAR(n) data types in SQL Server. That article even specifically mentions:
As you might be aware that the basic unit of storage in SQL Server is a page. The page size is 8 KB (8192 byes) in SQL Server, and it is fixed. On a page, SQL Server uses 96 bytes for the page header. We can store 8096 bytes ( 8192-96 bytes) for data in SQL Server. Apart from this, page also contains row overhead and row offset and leaves 8000 bytes to use for data storage. Due to this, we can store up to 8000 bytes of data using varchar (8000) data type.
Is this because the page size is 8192 Bytes?
Yes. In SQL Server version 7 the 8K page was introduced and varchar(n) max length went from 255 to 8000.
The page size before version 7 was 2K, so allowing a varchar to take up most of a page was new too.
varchar(255) field length comes from. I assumed it was just because it's a nice round number in base 2, rather than any limit imposed by (current) releases of SQL Server.
In addition to the other comments:
In SQL Server 2005, we got what is sometimes referred to as "Page Overflow" and sometimes "Row Overflow". This means that you can have, say, two varchar(8000) columns - both fully populated with data.
Now, 8000 + 8000 is of course more than 8000, so this won't fit on one page. When SQL Server construct a row in memory (insert/update), it will notice if the row size will be > 8000. If so, it will push one more more columns out-of-row onto row-over flow pages, leaving a 24 byte pointer behind.
These overflow pages are sometimes confused with LOB pages, for which the pointer is either 16 bytes (large values out of row = 1) or 24 bytes.
