Difference between varchar(max) and varchar(8000)

Question

Post reply

Difference between varchar(max) and varchar(8000)

Viewing 15 posts - 31 through 45 (of 56 total)

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

sattusarmaha SSC Rookie Points: 27 More actions · Answer 1

Hi Charles,

If Gail Shaw is wrong, can u pls giv me answer of below query -

Declare @a as NVARCHAR(max)

SET @a = replicate(convert(NVARCHAR(MAX), '¢¢¢¢¢¢¢¢¢¢'), 805)

select @a

select len(@A)

GO

Why @a gets more then 8000 ?

Satyanarayan V.

Mad Myche SSCommitted Points: 1593 More actions · Answer 2

sattusarmaha (3/1/2011)
Hi Charles,
If Gail Shaw is wrong, can u pls giv me answer of below query -
Declare @a as NVARCHAR(max)
SET @a = replicate(convert(NVARCHAR(MAX), '¢¢¢¢¢¢¢¢¢¢'), 805)
select @a
select len(@A)
GO
Why @a gets more then 8000 ?
Satyanarayan V.

Read the entire post, Charlesz said the max was 64k.

Director of Transmogrification Services

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

Please note: 2 year old thread

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

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 4

GilaMonster (3/3/2011)
Please note: 2 year old thread

Heh... yeah, but it's still fun. 😀

--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)

Mad Myche SSCommitted Points: 1593 More actions · Answer 5

Oh well... The only reason I came to it was the newsletter had it in it... Still learned from it so it's all good

Director of Transmogrification Services

jasonmorris SSCrazy Points: 2492 More actions · Answer 6

Now....

Who has the biggest Pork Chop Cannon?

Jeff perhaps?

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 7

Nah... no pork chops required on this friendly little thread. 🙂

--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)

RogueBlackSheep Valued Member Points: 63 More actions · Answer 8

when talking with rookies:

- teach them proper t-sql (not addon products!)

- show them ref online (msdn, http, books online, etc)

- do not confuse them with "products", show them code!!!

you are right displaying products there is a form of SPAM

please teach the rookies correctly, good habits from the start

haiao2000 Hall of Fame Points: 3734 More actions · Answer 9

DAMN!!!...I STILL DON'T KNOW WHAT PORK CHOP HAS TO DO WITH SQL. and it is alright that Microsoft Certified Master MVP be wrong once awhile, just like Microsoft once was wrong when it thought it could bring apple down to its knees 🙂

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

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)

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

haiao2000 Hall of Fame Points: 3734 More actions · Answer 11

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 SSC Guru Points: 1003863 More actions · Answer 12

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.

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)

praveshsinghfaq SSC Enthusiast Points: 134 More actions · Answer 13

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 SSC Guru Points: 1003863 More actions · Answer 14

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.

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)

praveshsinghfaq SSC Enthusiast Points: 134 More actions · Answer 15

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