Difference between varchar(max) and varchar(8000)

  • 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

  • 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.

  • Sounds like the beginning of a flame war.

    Let's keep our cool here.

    Regards

  • 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"

  • 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"

  • 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"

  • 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
  • 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.

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

  • 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
  • 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.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

  • 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.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

  • 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.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

  • 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"

  • 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.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance 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