10

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?

3 Answers 3

11

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.

14

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.

1
  • Ahhh, so that's where the commonly-seen 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.
    – Dai
    Feb 23 at 5:38
4

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.