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
SSChasing Mays
SSChasing Mays (609 reputation)SSChasing Mays (609 reputation)SSChasing Mays (609 reputation)SSChasing Mays (609 reputation)SSChasing Mays (609 reputation)SSChasing Mays (609 reputation)SSChasing Mays (609 reputation)SSChasing Mays (609 reputation)

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

Max
charlesz
charlesz
SSC-Enthusiastic
SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)

Group: General Forum Members
Points: 107 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
SSCertifiable
SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)

Group: General Forum Members
Points: 7562 Visits: 11793
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
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12406 Visits: 2048
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
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12406 Visits: 2048
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 (206 reputation)SSC Veteran (206 reputation)SSC Veteran (206 reputation)SSC Veteran (206 reputation)SSC Veteran (206 reputation)SSC Veteran (206 reputation)SSC Veteran (206 reputation)SSC Veteran (206 reputation)

Group: General Forum Members
Points: 206 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-Enthusiastic
SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)

Group: General Forum Members
Points: 107 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
SSC Eights!
SSC Eights! (979 reputation)SSC Eights! (979 reputation)SSC Eights! (979 reputation)SSC Eights! (979 reputation)SSC Eights! (979 reputation)SSC Eights! (979 reputation)SSC Eights! (979 reputation)SSC Eights! (979 reputation)

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

Let's keep our cool here.

Regards
SwePeso
SwePeso
SSCertifiable
SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)

Group: General Forum Members
Points: 5235 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
SSCertifiable
SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)

Group: General Forum Members
Points: 5235 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