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
Max-146500
Max-146500
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1959 Visits: 363
Ooh, I think I'm going to watch this one.

Max
charlesz
charlesz
SSC Veteran
SSC Veteran (223 reputation)SSC Veteran (223 reputation)SSC Veteran (223 reputation)SSC Veteran (223 reputation)SSC Veteran (223 reputation)SSC Veteran (223 reputation)SSC Veteran (223 reputation)SSC Veteran (223 reputation)

Group: General Forum Members
Points: 223 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.
Michael Valentine Jones
Michael Valentine Jones
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26914 Visits: 11930
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.
noeld
noeld
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39048 Visits: 2051
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
noeld
noeld
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39048 Visits: 2051
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
SQAPro
SQAPro
SSC Veteran
SSC Veteran (246 reputation)SSC Veteran (246 reputation)SSC Veteran (246 reputation)SSC Veteran (246 reputation)SSC Veteran (246 reputation)SSC Veteran (246 reputation)SSC Veteran (246 reputation)SSC Veteran (246 reputation)

Group: General Forum Members
Points: 246 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? Tongue

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
charlesz
charlesz
SSC Veteran
SSC Veteran (223 reputation)SSC Veteran (223 reputation)SSC Veteran (223 reputation)SSC Veteran (223 reputation)SSC Veteran (223 reputation)SSC Veteran (223 reputation)SSC Veteran (223 reputation)SSC Veteran (223 reputation)

Group: General Forum Members
Points: 223 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.
J-440512
J-440512
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2897 Visits: 949
Sounds like the beginning of a flame war.

Let's keep our cool here.

Regards
SwePeso
SwePeso
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16757 Visits: 3433
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"
SwePeso
SwePeso
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16757 Visits: 3433
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"
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