Max size varchar(max) in SQL Server 2000

Question

Hi all expert,

I would like to know what is the maximum size of varchar in SQL Server 2000. While I was googling somewhere its written 8000 characters and somewhere its written 8060 bytes. which one is correct??? Pls confirm me..

Thanks, Kumar

Answer 1

As far as I remember it is 8000 Chars.

For Nvarchar it is 4000.

Answer 2

Varchar is 8000 and nvarchar is 4000.

Here's the varchar msdn reference:
http://msdn.microsoft.com/en-us/library/aa258242(SQL.80).aspx

Answer 3

The maximum size of varchar(max) is large (2GB or 2Gchars, I think). The maximum value you can specify for n in varchar(n) is 8000, and for nvarchar it's 4000.

Answer 4

A SQL Server data page has 8k: 8192 bytes. From this a portion is reserved for the page header, leaving 8060 bytes the maximum lnegth a single row can have. In a row, the varbinary(n), varchar(n) and nvarchar(n) types cannot exceed 8000 bytes, which means that varchar(8000) is the maximum Ascii length and nvarchar(4000) is the maximum Unicode length (since Unicode stores each character on 2 bytes).

The best explanation of all these details comes from Inside the Storage Engine: Anatomy of a page.