Difference between varchar(max) and varchar(8000)

Question

Post reply

Difference between varchar(max) and varchar(8000)

jchandramouli

SSCommitted

Points: 1772
More actions
February 1, 2009 at 11:26 pm

#94964

Hi,
whats the actual difference between varchar(max) and varchar(8000). Is the difference applies same for nvarchar(max) and nvarchar(8000).
Thanks in advance.
"I Love Walking In The Rain So No One Can See Me Crying ! " ~ Charlie Chaplin

Viewing 15 posts - 1 through 15 (of 56 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Varchar(8000) stores a maximum of 8000 characters. Varchar(max) stores a maximum of 2 147 483 647 characters.

See Books Online, the page titled "char and varchar" for more info.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

John H Marsh SSChampion Points: 11671 More actions · Answer 2

Hello,

May be worthwhile taking a look at the following FAQ page, as it is a bit clearer than the BOL description:-

http://www.sql-server-helper.com/faq/sql-server-2005-varchar-max-p01.aspx

Regards,

John Marsh

www.sql.lu
SQL Server Luxembourg User Group

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 3

VARCHAR(MAX) uses the normal datapages until the content actually fills 8k of data.

When overflow happens, data is stored as old TEXT, IMAGE and a pointer is replacing the old content.

N 56°04'39.16"
E 12°55'05.25"

charlesz Old Hand Points: 381 More actions · Answer 4

First, you cannot have NVarchar(8000). Since varchar or NVarchar can only hold up to 8000 bytes, so the maximum length for NVarchar is 4000.

Second, NVarchar(MAX) and Varchar(MAX) can only hold up to 65535 bytes ( The number posted by Gail Shaw was wrong).

Third, the communication protocol of SQL Server (Tabular Data Stream (TDS) ) using Partially Length-Prefixed structure to encode lengths of "MAX" fields , if you mostly store very short strings in those fields, the overhead is significant.

Our product DB-WAN Accel communicates with SQL Server using TDS protocol.

Charles Zhang

http://www.speedydb.com

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 5

charlesz (2/2/2009)
Second, NVarchar(MAX) and Varchar(MAX) can only hold up to 65535 bytes ( The number posted by Gail Shaw was wrong).

Any bets on that?

DECLARE @test-2 VARCHAR(MAX),

@String VARCHAR(MAX)

SELECT @test-2 = '',

@String = '1234567890'

;WITH

cteTally AS

(--==== Create a Tally CTE from 1 to 10,000

SELECT TOP (10000)

ROW_NUMBER() OVER (ORDER BY t1.ID) AS N

FROM Master.sys.SysColumns t1

CROSS JOIN Master.sys.SysColumns t2

)

SELECT @test-2 = @test-2 + '1234567890'

FROM cteTally

SELECT LEN(@Test)

I have to ask... where did you get such an idea? I'd like to know so I can go straighten them out. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

Jeff Moden (2/2/2009)
charlesz (2/2/2009)
Second, NVarchar(MAX) and Varchar(MAX) can only hold up to 65535 bytes ( The number posted by Gail Shaw was wrong).
Any bets on that?
DECLARE @test-2 VARCHAR(MAX),
@String VARCHAR(MAX)
SELECT @test-2 = '',
@String = '1234567890'
;WITH
cteTally AS
(--==== Create a Tally CTE from 1 to 10,000
SELECT TOP (10000)
ROW_NUMBER() OVER (ORDER BY t1.ID) AS N
FROM Master.sys.SysColumns t1
CROSS JOIN Master.sys.SysColumns t2
)
SELECT @test-2 = @test-2 + '1234567890'
FROM cteTally
SELECT LEN(@Test)
I have to ask... where did you get such an idea? I'd like to know so I can go straighten them out. 😉

And now, direct from Book Online for viewing pleasure!

varchar [ ( n | max ) ]
Variable-length, non-Unicode character data. n can be a value from 1 through 8,000. max indicates that the maximum storage size is 2^31-1 bytes. The storage size is the actual length of data entered + 2 bytes. The data entered can be 0 characters in length. The SQL-2003 synonyms for varchar are char varying or character varying.

And

nvarchar [ ( n | max ) ]
Variable-length Unicode character data. n can be a value from 1 through 4,000. max indicates that the maximum storage size is 2^31-1 bytes. The storage size, in bytes, is two times the number of characters entered + 2 bytes. The data entered can be 0 characters in length. The SQL-2003 synonyms for nvarchar are national char varying and national character varying.

So, I must agree with Jeff, who and where did you here that so that they may be properly corrected. I see pork chops in someones future!

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

charlesz (2/2/2009)
Second, NVarchar(MAX) and Varchar(MAX) can only hold up to 65535 bytes ( The number posted by Gail Shaw was wrong).

Really? What makes you right and Books Online wrong?

Or is this just an excuse for you to advertise your product?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

jchandramouli SSCommitted Points: 1772 More actions · Answer 8

Thank you everyone.

"I Love Walking In The Rain So No One Can See Me Crying ! " ~ Charlie Chaplin

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 9

charlesz (2/2/2009)
Second, NVarchar(MAX) and Varchar(MAX) can only hold up to 65535 bytes ( The number posted by Gail Shaw was wrong).
Our product DB-WAN Accel communicates with SQL Server using TDS protocol.
Charles Zhang
http://www.speedydb.com

Lynn Pettis (2/2/2009)
So, I must agree with Jeff, who and where did you here that so that they may be properly corrected. I see pork chops in someones future!

And, since this is a form of "forum span", here's the first pork chop... why would anyone in their right mind even consider buying a product from a person who can't even get a grip on the basics of T-SQL... ie. the max values of data types?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Max-146500 SSCarpal Tunnel Points: 4691 More actions · Answer 10

Max-146500

SSCarpal Tunnel

Points: 4691

February 3, 2009 at 6:18 am

#937063

Ooh, I think I'm going to watch this one.

Max

charlesz Old Hand Points: 381 More actions · Answer 11

Sorry about my mistake. I should not trust document from Microsoft 100%. Here is a sentence from "Tabular Data Stream Protocol Specification".

"A type with unlimited max size, known as varchar(max), varbinary(max), nvarchar(max), which has a max size of 0xFFFF,..."

You can get the specification from http://download.microsoft.com/download/a/e/6/ae6e4142-aa58-45c6-8dcf-a657e5900cd3/%5BMS-TDS%5D.pdf (page 36)

By the way, Our product communicate with SQL Server using TDS protocol, we do not really care about how long varchar(max) and nvarchar(max) columns can hold.

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 12

charlesz (2/3/2009)
Sorry about my mistake. I should not trust document from Microsoft 100%. Here is a sentence from "Tabular Data Stream Protocol Specification".
"A type with unlimited max size, known as varchar(max), varbinary(max), nvarchar(max), which has a max size of 0xFFFF,..."
You can get the specification from http://download.microsoft.com/download/a/e/6/ae6e4142-aa58-45c6-8dcf-a657e5900cd3/%5BMS-TDS%5D.pdf (page 36)
By the way, Our product communicate with SQL Server using TDS protocol, we do not really care about how long varchar(max) and nvarchar(max) columns can hold.

Maybe you should avoid using documentation of the "Tabular Data Stream Protocol Specification" to answer questions about how the SQL Server 2005 Database Engine works, instead of the actual SQL Server 2005 Books Online Documentation.

noeld SSC Guru Points: 96590 More actions · Answer 13

Jeff Moden (2/3/2009)
charlesz (2/2/2009)
Second, NVarchar(MAX) and Varchar(MAX) can only hold up to 65535 bytes ( The number posted by Gail Shaw was wrong).
Our product DB-WAN Accel communicates with SQL Server using TDS protocol.
Charles Zhang
http://www.speedydb.com
Lynn Pettis (2/2/2009)
So, I must agree with Jeff, who and where did you here that so that they may be properly corrected. I see pork chops in someones future!
And, since this is a form of "forum span", here's the first pork chop... why would anyone in their right mind even consider buying a product from a person who can't even get a grip on the basics of T-SQL... ie. the max values of data types?

++1

I could not have replied any better!

* Noel

noeld SSC Guru Points: 96590 More actions · Answer 14

By the way, Our product communicate with SQL Server using TDS protocol, we do not really care about how long varchar(max) and nvarchar(max) columns can hold.

Really ?

Keep all those buffers with infinite length in memory your product is going very well.

* Noel