February 3, 2009 at 8:22 pm
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? 😛
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
February 3, 2009 at 9:47 pm
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.
February 4, 2009 at 6:02 am
Sounds like the beginning of a flame war.
Let's keep our cool here.
Regards
February 4, 2009 at 6:12 am
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"
February 4, 2009 at 6:23 am
An example...
-- Prepare sample data
CREATE TABLECharlesZ
(
i INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
ss VARCHAR(MAX) NOT NULL
)
-- Insert short string
INSERTCharlesZ
SELECT'Peter Larsson'
-- Insert long string
DECLARE@ls VARCHAR(MAX)
SET@ls = REPLICATE('Z', 8000)
SET@ls = @ls + REPLICATE('Z', 8000)
INSERTCharlesZ
SELECT@ls
-- Display table data
SELECTi,
ss,
DATALENGTH(ss) AS Characters
FROMCharlesZ
-- 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"
February 4, 2009 at 6:28 am
Another test
-- Prepare sample data
CREATE TABLECharlesZ
(
i INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
ss VARCHAR(MAX) NOT NULL
)
-- Display index page information
DBCC IND(Test, CharlesZ, 1)
-- Insert short string
INSERTCharlesZ
SELECT'Peter Larsson'
-- Display table data
SELECTi,
ss,
DATALENGTH(ss) AS Characters
FROMCharlesZ
-- Display index page information
DBCC IND(Test, CharlesZ, 1)
-- Insert long string
DECLARE@ls VARCHAR(MAX)
SET@ls = REPLICATE('Z', 8000)
SET@ls = @ls + REPLICATE('Z', 8000)
INSERTCharlesZ
SELECT@ls
-- Display table data
SELECTi,
ss,
DATALENGTH(ss) AS Characters
FROMCharlesZ
-- Display index page information
DBCC IND(Test, CharlesZ, 1)
-- Clean up
DROP TABLE CharlesZ
N 56°04'39.16"
E 12°55'05.25"
February 4, 2009 at 9:08 am
charlesz (2/3/2009)
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.
And this is relevant to a discussion on the size of columns in the storage engine how?
You product advertising is inappropriate, seeing as the question has nothing to do with the TDS protocol. Reported to mod.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 4, 2009 at 11:16 am
Sorry 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.
February 4, 2009 at 11:20 am
(whispering..) I see high velocity pork chops....
February 4, 2009 at 11:24 am
charlesz (2/4/2009)
I do not want to get to the details about our product.
Then why did you post info about it in your first post on this thread?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 4, 2009 at 7:04 pm
J (2/4/2009)
Sounds like the beginning of a flame war.Let's keep our cool here.
Regards
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.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 4, 2009 at 7:07 pm
Lynn Pettis (2/4/2009)
(whispering..) I see high velocity pork chops....
(whispering wilst loading the pork chop cannon) C'mon target... stand up again... 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
February 4, 2009 at 7:11 pm
charlesz (2/3/2009)
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.
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.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 5, 2009 at 12:42 am
Let these links end this discussion
SQL Server 2005
http://msdn.microsoft.com/en-us/library/ms143432(SQL.90).aspx
SQL Server 2008
http://msdn.microsoft.com/en-us/library/ms143432.aspx
SQL Server 2000
http://msdn.microsoft.com/en-us/library/aa933149(SQL.80).aspx
N 56°04'39.16"
E 12°55'05.25"
February 5, 2009 at 5:28 am
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.
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. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 16 through 30 (of 56 total)
You must be logged in to reply to this topic. Login to reply