SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Difference between varchar(max) and varchar(8000)


Difference between varchar(max) and varchar(8000)

Author
Message
haiao2000
haiao2000
Old Hand
Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)

Group: General Forum Members
Points: 312 Visits: 438
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 :-)
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)

Group: General Forum Members
Points: 85274 Visits: 41078
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
praveshsinghfaq
praveshsinghfaq
SSC Rookie
SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)

Group: General Forum Members
Points: 26 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
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)

Group: General Forum Members
Points: 85274 Visits: 41078
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
praveshsinghfaq
praveshsinghfaq
SSC Rookie
SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)

Group: General Forum Members
Points: 26 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
Cyberspy
Cyberspy
Valued Member
Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)

Group: General Forum Members
Points: 52 Visits: 75
Hi,

I know this post is now 5 1/2 years old but, as Jeff says, it's fun... Whistling

I found this post looking for an answer to the original question, and now know far more about it than I expected to, which is great.

Why am I posting though?
I had a look at Charesz's web site (http://www.speedydb.com ) out of curiosity and it seems like common sense has won the day.
It's now just a generic holding page: seems like SpeedyDB is no more.

SQL Server Central rocks! :-D
dennisp
dennisp
SSC-Addicted
SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)

Group: General Forum Members
Points: 470 Visits: 128
Hey Gail, my memory is failing me.
What are the reasons again to put varchar(max)'s at the end of the table.
I remember Kimberly and/or Kalen talking about this years ago and just don't recall.

Dennis Parks
MCSE, MCDBA, MCSD, MCAD, MCTS
kevin dockerty
kevin dockerty
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 44
Varchar(MAX) is 2GB for sure.

Kevin Dockerty
SQL Developer, DBA and website owner
t-sql developer forum
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search