Difference between varchar(max) and varchar(8000)

  • SQAPro

    Right there with Babe

    Points: 742

    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

  • charlesz

    Old Hand

    Points: 381

    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

    SSCertifiable

    Points: 6285

    Sounds like the beginning of a flame war.

    Let's keep our cool here.

    Regards

  • SwePeso

    SSC-Dedicated

    Points: 39693

    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

    SSC-Dedicated

    Points: 39693

    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"

  • SwePeso

    SSC-Dedicated

    Points: 39693

    Another test

    -- Prepare sample data

    CREATE TABLE CharlesZ

    (

    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

    INSERT CharlesZ

    SELECT 'Peter Larsson'

    -- Display table data

    SELECT i,

    ss,

    DATALENGTH(ss) AS Characters

    FROM CharlesZ

    -- 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)

    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)

    -- Clean up

    DROP TABLE CharlesZ


    N 56°04'39.16"
    E 12°55'05.25"

  • Gail Shaw

    SSC Guru

    Points: 1004446

    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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • charlesz

    Old Hand

    Points: 381

    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.

  • Lynn Pettis

    SSC Guru

    Points: 442144

    (whispering..) I see high velocity pork chops....

  • Gail Shaw

    SSC Guru

    Points: 1004446

    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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Jeff Moden

    SSC Guru

    Points: 994866

    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


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • Jeff Moden

    SSC Guru

    Points: 994866

    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


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • Jeff Moden

    SSC Guru

    Points: 994866

    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


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • SwePeso

    SSC-Dedicated

    Points: 39693

    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"

  • Jeff Moden

    SSC Guru

    Points: 994866

    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


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

Viewing 15 posts - 16 through 30 (of 57 total)

You must be logged in to reply to this topic. Login to reply