InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 23 May 2013 08:26:25 GMT20http://www.sqlservercentral.com/Forums/Topic647815-145-1.aspxDifference between Varchar(8000) and Varchar(MAX)Varchar(8000) stores a maximum of 8000 characters, andVarchar(MAX) stores a maximum of 2,147,483,647 charactersIt supported in SQL Server 2005 or above versionTue, 07 May 2013 03:21:14 GMTpraveshsinghfaqhttp://www.sqlservercentral.com/Forums/Topic647815-145-1.aspx[quote][b]praveshsinghfaq (5/6/2013)[/b][hr]Hi!You can get the help from the following link. I hope it very helpful for you.[url=http://www.mindstick.com/Blog/479/Difference%20between%20char%20var]http://www.mindstick.com/Blog/479/Difference%20between%20char%20var[/url]Thanks & Regard!Pravesh Singh[/quote]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. ;-)Mon, 06 May 2013 09:32:52 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic647815-145-1.aspxHi!You can get the help from the following link. I hope it very helpful for you.[url=http://www.mindstick.com/Blog/479/Difference%20between%20char%20var]http://www.mindstick.com/Blog/479/Difference%20between%20char%20var[/url]Thanks & Regard!Pravesh SinghMon, 06 May 2013 08:17:43 GMTpraveshsinghfaqhttp://www.sqlservercentral.com/Forums/Topic647815-145-1.aspx[quote][b]chetan.aegis (12/14/2012)[/b][hr]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.[/quote]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.Fri, 14 Dec 2012 06:51:47 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic647815-145-1.aspx[quote][b]GilaMonster (11/28/2012)[/b][hr]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)[/quote]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 :-)Wed, 28 Nov 2012 13:39:03 GMThaiao2000http://www.sqlservercentral.com/Forums/Topic647815-145-1.aspxPlease 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)Wed, 28 Nov 2012 13:24:01 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic647815-145-1.aspxDAMN!!!...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 :-)Wed, 28 Nov 2012 12:44:36 GMThaiao2000http://www.sqlservercentral.com/Forums/Topic647815-145-1.aspxwhen 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 SPAMplease teach the rookies correctly, good habits from the startMon, 07 Mar 2011 11:35:50 GMTRogueBlackSheephttp://www.sqlservercentral.com/Forums/Topic647815-145-1.aspxNah... no pork chops required on this friendly little thread. :-)Mon, 07 Mar 2011 06:23:41 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic647815-145-1.aspxNow....Who has the biggest Pork Chop Cannon?Jeff perhaps?Mon, 07 Mar 2011 04:42:29 GMTjasonmorrishttp://www.sqlservercentral.com/Forums/Topic647815-145-1.aspxOh well... The only reason I came to it was the newsletter had it in it... Still learned from it so it's all goodFri, 04 Mar 2011 08:32:36 GMTMad Mychehttp://www.sqlservercentral.com/Forums/Topic647815-145-1.aspx[quote][b]GilaMonster (3/3/2011)[/b][hr]Please note: 2 year old thread[/quote]Heh... yeah, but it's still fun. :-DThu, 03 Mar 2011 20:27:12 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic647815-145-1.aspxPlease note: 2 year old threadThu, 03 Mar 2011 17:04:28 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic647815-145-1.aspx[quote][b]sattusarmaha (3/1/2011)[/b][hr]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)GOWhy @A gets more then 8000 ?Satyanarayan V.[/quote]Read the entire post, Charlesz said the max was 64k.Thu, 03 Mar 2011 08:59:35 GMTMad Mychehttp://www.sqlservercentral.com/Forums/Topic647815-145-1.aspxHi 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)GOWhy @A gets more then 8000 ?Satyanarayan V.Tue, 01 Mar 2011 23:33:32 GMTsattusarmahahttp://www.sqlservercentral.com/Forums/Topic647815-145-1.aspx[quote][b]charlesz (2/3/2009)[/b][hr]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 [url]http://download.microsoft.com/download/a/e/6/ae6e4142-aa58-45c6-8dcf-a657e5900cd3/%5BMS-TDS%5D.pdf[/url] (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.[/quote]Ah... I get it and understand your confusion. The problem is that VARCHAR(MAX) is not classified as a "char" datatype... it's actually considered to be a "text" datatype. The problem is, they don't say that in that spot in the document.In the future, though, when you say someone is wrong, it's always good to produce the proof either in the form of code or a bonafide MS document. Saves a lot of time and keeps folks from having to add water cooling to their pork chop cannons. ;)Thu, 05 Feb 2009 05:28:20 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic647815-145-1.aspxLet these links end this discussionSQL Server 2005[url=http://msdn.microsoft.com/en-us/library/ms143432(SQL.90).aspx]http://msdn.microsoft.com/en-us/library/ms143432(SQL.90).aspx[/url]SQL Server 2008[url=http://msdn.microsoft.com/en-us/library/ms143432.aspx]http://msdn.microsoft.com/en-us/library/ms143432.aspx[/url]SQL Server 2000[url=http://msdn.microsoft.com/en-us/library/aa933149(SQL.80).aspx]http://msdn.microsoft.com/en-us/library/aa933149(SQL.80).aspx[/url]Thu, 05 Feb 2009 00:42:55 GMTSwePesohttp://www.sqlservercentral.com/Forums/Topic647815-145-1.aspx[quote][b]charlesz (2/3/2009)[/b][hr]The contents of Varchar(MAX), NVarchar(MAX), VarBinary(MAX), and XML fields are encoded using "Partially Length-Prefixed" (PLP in short). A PLP stream starts with total length (8 bytes), followed by current length ( 4 bytes, the length in the current packet) then followed by the actual contents. When a column contains more than one packet (the maximum packet size is 65535) can hold, a number of "PLP" streams will be sent from server to client. Our product is called "DB-WAN Accel", it caches/compress/decompress the query results to speed up the data transfer over wide area networks. It does not really care how big VARCHAR(MAX) columns are.As I said in previous post, for a column mostly stores very short strings, the overhead of Varchar(MAX) is significant.[/quote]And absolutely none of that has to do with the number of bytes that VARCHAR(MAX) can hold which is exactly as Gail posted... not the 65535 you said was it's limit.Wed, 04 Feb 2009 19:11:05 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic647815-145-1.aspx[quote][b]Lynn Pettis (2/4/2009)[/b][hr](whispering..) I see high velocity pork chops....[/quote](whispering wilst loading the pork chop cannon) [size="1"][i]C'mon target... stand up again... [/i];)[/size]Wed, 04 Feb 2009 19:07:03 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic647815-145-1.aspx[quote][b]J (2/4/2009)[/b][hr]Sounds like the beginning of a flame war.Let's keep our cool here.Regards[/quote]It's hard to keep cool when some forum spammer posts bum information, says that an intelligent member of the forum is wrong when they're not, and then back-peddles by blaming the bum answer on bloody packet size.Wed, 04 Feb 2009 19:04:26 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic647815-145-1.aspx[quote][b]charlesz (2/4/2009)[/b][hr]I do not want to get to the details about our product. [/quote]Then why did you post info about it in your first post on this thread?Wed, 04 Feb 2009 11:24:29 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic647815-145-1.aspx(whispering..) I see high velocity pork chops....Wed, 04 Feb 2009 11:20:46 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic647815-145-1.aspxSorry about that.I do not want to get to the details about our product. However, I was blamed when I said in our product we do not care about the size of VARCHAR(MAX), just for clarification for blame of buffer over run by other post.Wed, 04 Feb 2009 11:16:55 GMTcharleszhttp://www.sqlservercentral.com/Forums/Topic647815-145-1.aspx[quote][b]charlesz (2/3/2009)[/b][hr]The contents of Varchar(MAX), NVarchar(MAX), VarBinary(MAX), and XML fields are encoded using "Partially Length-Prefixed" (PLP in short). A PLP stream starts with total length (8 bytes), followed by current length ( 4 bytes, the length in the current packet) then followed by the actual contents. When a column contains more than one packet (the maximum packet size is 65535) can hold, a number of "PLP" streams will be sent from server to client. [/quote]And this is relevant to a discussion on the size of columns in the [b]storage engine[/b] how?You product advertising is inappropriate, seeing as the question has nothing to do with the TDS protocol. Reported to mod.Wed, 04 Feb 2009 09:08:24 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic647815-145-1.aspxAnother test[code]-- Prepare sample dataCREATE TABLE CharlesZ ( i INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED, ss VARCHAR(MAX) NOT NULL )-- Display index page informationDBCC IND(Test, CharlesZ, 1)-- Insert short stringINSERT CharlesZSELECT 'Peter Larsson'-- Display table dataSELECT i, ss, DATALENGTH(ss) AS CharactersFROM CharlesZ-- Display index page informationDBCC IND(Test, CharlesZ, 1)-- Insert long stringDECLARE @ls VARCHAR(MAX)SET @ls = REPLICATE('Z', 8000)SET @ls = @ls + REPLICATE('Z', 8000)INSERT CharlesZSELECT @ls-- Display table dataSELECT i, ss, DATALENGTH(ss) AS CharactersFROM CharlesZ-- Display index page informationDBCC IND(Test, CharlesZ, 1)-- Clean upDROP TABLE CharlesZ[/code]Wed, 04 Feb 2009 06:28:01 GMTSwePesohttp://www.sqlservercentral.com/Forums/Topic647815-145-1.aspxAn example...[code]-- Prepare sample dataCREATE TABLE CharlesZ ( i INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED, ss VARCHAR(MAX) NOT NULL )-- Insert short stringINSERT CharlesZSELECT 'Peter Larsson'-- Insert long stringDECLARE @ls VARCHAR(MAX)SET @ls = REPLICATE('Z', 8000)SET @ls = @ls + REPLICATE('Z', 8000)INSERT CharlesZSELECT @ls-- Display table dataSELECT i, ss, DATALENGTH(ss) AS CharactersFROM CharlesZ-- Display index page informationDBCC IND(Test, CharlesZ, 1)-- Display in-row dataDBCC PAGE(Test, 1, 196, 3) WITH TABLERESULTSDROP TABLE CharlesZ[/code]Wed, 04 Feb 2009 06:23:31 GMTSwePesohttp://www.sqlservercentral.com/Forums/Topic647815-145-1.aspx[quote][b]charlesz (2/3/2009)[/b][hr]As I said in previous post, for a column mostly stores very short strings, the overhead of Varchar(MAX) is significant.[/quote]There is no overhead when the "string size" is less than 8000 bytes.Wed, 04 Feb 2009 06:12:14 GMTSwePesohttp://www.sqlservercentral.com/Forums/Topic647815-145-1.aspxSounds like the beginning of a flame war.Let's keep our cool here.RegardsWed, 04 Feb 2009 06:02:49 GMTJ-440512http://www.sqlservercentral.com/Forums/Topic647815-145-1.aspxThe contents of Varchar(MAX), NVarchar(MAX), VarBinary(MAX), and XML fields are encoded using "Partially Length-Prefixed" (PLP in short). A PLP stream starts with total length (8 bytes), followed by current length ( 4 bytes, the length in the current packet) then followed by the actual contents. When a column contains more than one packet (the maximum packet size is 65535) can hold, a number of "PLP" streams will be sent from server to client. Our product is called "DB-WAN Accel", it caches/compress/decompress the query results to speed up the data transfer over wide area networks. It does not really care how big VARCHAR(MAX) columns are.As I said in previous post, for a column mostly stores very short strings, the overhead of Varchar(MAX) is significant.Tue, 03 Feb 2009 21:47:26 GMTcharleszhttp://www.sqlservercentral.com/Forums/Topic647815-145-1.aspx[quote][b]noeld (2/3/2009)[/b][hr][quote]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.[/quote]Really ?Keep all those buffers with infinite length in memory your product is going very well.[/quote] heh ++ {pulls out his buffer overrun test data, rubs his hands gleefully and cackles an evil laugh} Lets see how that app handles a 1.1megachar string shall we? :POh and the Storage size is 2^31-1 but we're not talking 8 bit ascii are we? so remember that the actual max Length for anything you stuff in there is going to be 1,073,741,822Tue, 03 Feb 2009 20:22:41 GMTSQAProhttp://www.sqlservercentral.com/Forums/Topic647815-145-1.aspx[quote]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.[/quote]Really ?Keep all those buffers with infinite length in memory your product is going very well.Tue, 03 Feb 2009 16:10:06 GMTnoeldhttp://www.sqlservercentral.com/Forums/Topic647815-145-1.aspx[quote][b]Jeff Moden (2/3/2009)[/b][hr][quote][b]charlesz (2/2/2009)[/b][hr]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[url]http://www.speedydb.com[/url][/quote][quote][b]Lynn Pettis (2/2/2009)[/b][hr]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![/quote]And, since this is a form of "forum span", here's the first pork chop... [size="5"][b]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?[/b][/size][/quote][size="5"][b]++1[/b][/size]I could not have replied any better!Tue, 03 Feb 2009 16:07:31 GMTnoeldhttp://www.sqlservercentral.com/Forums/Topic647815-145-1.aspx[quote][b]charlesz (2/3/2009)[/b][hr]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 [url]http://download.microsoft.com/download/a/e/6/ae6e4142-aa58-45c6-8dcf-a657e5900cd3/%5BMS-TDS%5D.pdf[/url] (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.[/quote]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.Tue, 03 Feb 2009 15:16:12 GMTMichael Valentine Joneshttp://www.sqlservercentral.com/Forums/Topic647815-145-1.aspxSorry 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 [url]http://download.microsoft.com/download/a/e/6/ae6e4142-aa58-45c6-8dcf-a657e5900cd3/%5BMS-TDS%5D.pdf[/url] (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.Tue, 03 Feb 2009 14:43:38 GMTcharleszhttp://www.sqlservercentral.com/Forums/Topic647815-145-1.aspxOoh, I think I'm going to watch this one.Tue, 03 Feb 2009 06:18:05 GMTMax-146500http://www.sqlservercentral.com/Forums/Topic647815-145-1.aspx[quote][b]charlesz (2/2/2009)[/b][hr]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[url]http://www.speedydb.com[/url][/quote][quote][b]Lynn Pettis (2/2/2009)[/b][hr]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![/quote]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?Tue, 03 Feb 2009 06:02:48 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic647815-145-1.aspxThank you everyone.Mon, 02 Feb 2009 22:28:18 GMTjchandramoulihttp://www.sqlservercentral.com/Forums/Topic647815-145-1.aspx[quote][b]charlesz (2/2/2009)[/b][hr]Second, NVarchar(MAX) and Varchar(MAX) can only hold up to 65535 bytes ( The number posted by Gail Shaw was wrong).[/quote]Really? What makes you right and Books Online wrong?Or is this just an excuse for you to advertise your product?Mon, 02 Feb 2009 21:58:36 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic647815-145-1.aspx[quote][b]Jeff Moden (2/2/2009)[/b][hr][quote][b]charlesz (2/2/2009)[/b][hr]Second, NVarchar(MAX) and Varchar(MAX) can only hold up to 65535 bytes ( The number posted by Gail Shaw was wrong).[/quote]Any bets on that?[code]DECLARE @Test VARCHAR(MAX), @String VARCHAR(MAX) SELECT @Test = '', @String = '1234567890';WITHcteTally 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)[/code]I have to ask... where did you get such an idea? I'd like to know so I can go straighten them out. ;)[/quote]And now, direct from Book Online for viewing pleasure![quote]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.[/quote]And[quote]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.[/quote]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!Mon, 02 Feb 2009 20:47:28 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic647815-145-1.aspx[quote][b]charlesz (2/2/2009)[/b][hr]Second, NVarchar(MAX) and Varchar(MAX) can only hold up to 65535 bytes ( The number posted by Gail Shaw was wrong).[/quote]Any bets on that?[code]DECLARE @Test VARCHAR(MAX), @String VARCHAR(MAX) SELECT @Test = '', @String = '1234567890';WITHcteTally 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)[/code]I have to ask... where did you get such an idea? I'd like to know so I can go straighten them out. ;)Mon, 02 Feb 2009 20:19:20 GMTJeff Moden