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 Tuesday, February 3, 2009 6:18 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 7:07 AM
Points: 247, Visits: 356
Ooh, I think I'm going to watch this one.

Max
Post #648769
Posted Tuesday, February 3, 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
Posted Tuesday, February 3, 2009 3:16 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 11:09 PM
Points: 3,108, Visits: 11,502
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
Posted Tuesday, February 3, 2009 4:07 PM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 5:47 AM
Points: 6,259, Visits: 2,029
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
Posted Tuesday, February 3, 2009 4:10 PM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 5:47 AM
Points: 6,259, Visits: 2,029
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
Posted Tuesday, February 3, 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
Posted Tuesday, February 3, 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
Posted Wednesday, February 4, 2009 6:02 AM
SSC-Addicted

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

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 9:05 PM
Points: 441, Visits: 933
Sounds like the beginning of a flame war.

Let's keep our cool here.

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


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Sunday, September 28, 2014 12:23 AM
Points: 2,397, Visits: 3,411
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
Posted Wednesday, February 4, 2009 6:23 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Sunday, September 28, 2014 12:23 AM
Points: 2,397, Visits: 3,411
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 ««12345»»»

Permissions Expand / Collapse