Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12345»»»

Difference between varchar(max) and varchar(8000) Expand / Collapse
Author
Message
Posted Sunday, February 1, 2009 11:26 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, November 10, 2014 7:52 AM
Points: 42, Visits: 142
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
Post #647815
Posted Sunday, February 1, 2009 11:41 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 7:03 AM
Points: 40,400, Visits: 36,840
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 2008, MVP
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

Post #647826
Posted Sunday, February 1, 2009 11:46 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, April 22, 2014 6:56 AM
Points: 1,330, Visits: 1,434
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
Post #647827
Posted Monday, February 2, 2009 7:56 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, October 28, 2014 11:02 AM
Points: 2,397, Visits: 3,419
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"
Post #648042
Posted Monday, February 2, 2009 7:47 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, April 28, 2009 4:43 PM
Points: 51, Visits: 37
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
Post #648541
Posted Monday, February 2, 2009 8:19 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:07 AM
Points: 35,552, Visits: 32,149
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   VARCHAR(MAX),
@String VARCHAR(MAX)

SELECT @Test = '',
@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 = @Test + '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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #648549
Posted Monday, February 2, 2009 8:47 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 4:21 AM
Points: 20,801, Visits: 32,723
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   VARCHAR(MAX),
@String VARCHAR(MAX)

SELECT @Test = '',
@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 = @Test + '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!




Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #648554
Posted Monday, February 2, 2009 9:58 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 7:03 AM
Points: 40,400, Visits: 36,840
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 2008, MVP
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

Post #648568
Posted Monday, February 2, 2009 10:28 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, November 10, 2014 7:52 AM
Points: 42, Visits: 142
Thank you everyone.

"I Love Walking In The Rain So No One Can See Me Crying ! " ~ Charlie Chaplin
Post #648576
Posted Tuesday, February 3, 2009 6:02 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:07 AM
Points: 35,552, Visits: 32,149
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #648756
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse