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 Wednesday, November 28, 2012 1:39 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 2:31 PM
Points: 151, Visits: 391
GilaMonster (11/28/2012)
Please note: 3 year old thread (and what I was in this thread was incorrectly accused of being wrong. I've been wrong elsewhere, but not here)



I was aware of the aging thread...but just like Jeff said...it was still fun to read ...at the same time checking to see if everyone still alive, many things could happen in 3 years
Post #1390103
Posted Friday, December 14, 2012 6:51 AM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 11:15 PM
Points: 36,751, Visits: 31,201
chetan.aegis (12/14/2012)
sql server 2005 annot exceed 8000 bytes in size.variable binary column can store 8000 bytes assuming in a table.And were as var char can store 2 bytes per Unicode character. The actual storage size of the var char(max) is length of data entered + 2.VARCHAR data type uses one byte, the maximum length for a VARCHAR(MAX) data type is 2,147,483,645.


Not quite right. First (if we're going to deal with exact byte counts), the VARCHAR(MAX) datatype holds 2^31-1 bytes. A couple of entires into a scientific calculator says the actual answer is 2,147,483,647. You might have just phat phingered that last digit.

I'm also not sure what you mean by "sql server 2005 annot [sic] exceed 8000 bytes in size" because 2005 was the first version to come out with the MAX datatype capabilities. That includes VARCHAR, NVARCHAR, and VARBINARY. CHAR, NCHAR, and BINARY can only hold 8000 bytes each with the understanding that that will only be 4000 characters in NCHAR.

There's something else (and I may have missed it above)... The use of any "blob" datatype (which includes any datatype defined as MAX, XML, IMAGE, ect) will prevent online rebuilds of indexes if the "blob" column(s) are contained within the index in 2005 and 2008. That means that you won't be able to rebuild the Clustered Index of a table if the table contains a "blob" column. Of course, any Non-Clustered index with an "include" on a "blob" column will suffer the same fate. IIRC, they've fixed this "little" problem in 2012.


--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 #1396625
Posted Monday, May 6, 2013 8:17 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, May 7, 2013 3:20 AM
Points: 2, Visits: 1
Hi!

You can get the help from the following link. I hope it very helpful for you.

http://www.mindstick.com/Blog/479/Difference%20between%20char%20var

Thanks & Regard!
Pravesh Singh
Post #1449733
Posted Monday, May 6, 2013 9:32 AM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 11:15 PM
Points: 36,751, Visits: 31,201
praveshsinghfaq (5/6/2013)
Hi!

You can get the help from the following link. I hope it very helpful for you.

http://www.mindstick.com/Blog/479/Difference%20between%20char%20var

Thanks & Regard!
Pravesh Singh


It helps but not with the subject at hand because it doesn't explain any of the MAX datatypes, which was the subject of this entire post.


--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 #1449760
Posted Tuesday, May 7, 2013 3:21 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, May 7, 2013 3:20 AM
Points: 2, Visits: 1
Difference between Varchar(8000) and Varchar(MAX)

Varchar(8000) stores a maximum of 8000 characters, and

Varchar(MAX) stores a maximum of 2,147,483,647 characters

It supported in SQL Server 2005 or above version
Post #1450036
« Prev Topic | Next Topic »

Add to briefcase «««12345

Permissions Expand / Collapse