Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » SQL Server 2005 » Development » Difference between varchar(max) and...
Add to briefcase
45 posts, Page 2 of 5
««12345»»»

Difference between varchar(max) and varchar(8000) Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
Max-146500
Posted Tuesday, February 03, 2009 6:18 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, February 12, 2013 8:35 AM
Points: 271, Visits: 314
Ooh, I think I'm going to watch this one.

Max
Post #648769
charlesz
Posted Tuesday, February 03, 2009 2:43 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
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 http://download.microsoft.com/download/a/e/6/ae6e4142-aa58-45c6-8dcf-a657e5900cd3/%5BMS-TDS%5D.pdf (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.
Post #649372
Michael Valentine Jones
Posted Tuesday, February 03, 2009 3:16 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 2:22 PM
Points: 2,944, Visits: 10,508
charlesz (2/3/2009)
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 http://download.microsoft.com/download/a/e/6/ae6e4142-aa58-45c6-8dcf-a657e5900cd3/%5BMS-TDS%5D.pdf (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.



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.
Post #649396
noeld
Posted Tuesday, February 03, 2009 4:07 PM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 4:39 PM
Points: 6,260, Visits: 1,977
Jeff Moden (2/3/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).
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?


++1

I could not have replied any better!


* Noel
Post #649427
noeld
Posted Tuesday, February 03, 2009 4:10 PM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 4:39 PM
Points: 6,260, Visits: 1,977
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.


Really ?

Keep all those buffers with infinite length in memory your product is going very well.


* Noel
Post #649430
SQAPro
Posted Tuesday, February 03, 2009 8:22 PM


SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Tuesday, December 14, 2010 4:03 PM
Points: 164, Visits: 143
noeld (2/3/2009)
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.


Really ?

Keep all those buffers with infinite length in memory your product is going very well.



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? :P

Oh 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,822
Post #649503
charlesz
Posted Tuesday, February 03, 2009 9: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
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.
Post #649521
J-440512
Posted Wednesday, February 04, 2009 6:02 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, September 14, 2009 5:22 AM
Points: 438, Visits: 918
Sounds like the beginning of a flame war.

Let's keep our cool here.

Regards
Post #649721
SwePeso
Posted Wednesday, February 04, 2009 6:12 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, May 06, 2013 1:26 PM
Points: 2,359, Visits: 3,292
charlesz (2/3/2009)

As I said in previous post, for a column mostly stores very short strings, the overhead of Varchar(MAX) is significant.
There is no overhead when the "string size" is less than 8000 bytes.

N 56°04'39.16"
E 12°55'05.25"
Post #649726
SwePeso
Posted Wednesday, February 04, 2009 6:23 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, May 06, 2013 1:26 PM
Points: 2,359, Visits: 3,292
An example...
-- Prepare sample data
CREATE TABLE	CharlesZ
		(
			i INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
			ss VARCHAR(MAX) NOT NULL
		)

-- Insert short string
INSERT	CharlesZ
SELECT	'Peter Larsson'

-- Insert long string
DECLARE	@ls VARCHAR(MAX)

SET	@ls = REPLICATE('Z', 8000)
SET	@ls = @ls + REPLICATE('Z', 8000)

INSERT	CharlesZ
SELECT	@ls

-- Display table data
SELECT	i,
	ss,
	DATALENGTH(ss) AS Characters
FROM	CharlesZ

-- Display index page information
DBCC IND(Test, CharlesZ, 1)

-- Display in-row data
DBCC PAGE(Test, 1, 196, 3) WITH TABLERESULTS

DROP TABLE CharlesZ

N 56°04'39.16"
E 12°55'05.25"
Post #649735
« Prev Topic | Next Topic »

Add to briefcase
45 posts, Page 2 of 5
««12345»»»

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.