Difference between varchar(max) and varchar(8000)

  • Hi,

    whats the actual difference between varchar(max) and varchar(8000). Is the difference applies same for nvarchar(max) and nvarchar(8000).

    Thanks in advance.

    "I Love Walking In The Rain So No One Can See Me Crying ! " ~ Charlie Chaplin

  • Varchar(8000) stores a maximum of 8000 characters. Varchar(max) stores a maximum of 2 147 483 647 characters.

    See Books Online, the page titled "char and varchar" for more info.

    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
  • Hello,

    May be worthwhile taking a look at the following FAQ page, as it is a bit clearer than the BOL description:-

    http://www.sql-server-helper.com/faq/sql-server-2005-varchar-max-p01.aspx

    Regards,

    John Marsh

    www.sql.lu
    SQL Server Luxembourg User Group

  • VARCHAR(MAX) uses the normal datapages until the content actually fills 8k of data.

    When overflow happens, data is stored as old TEXT, IMAGE and a pointer is replacing the old content.


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

  • First, you cannot have NVarchar(8000). Since varchar or NVarchar can only hold up to 8000 bytes, so the maximum length for NVarchar is 4000.

    Second, NVarchar(MAX) and Varchar(MAX) can only hold up to 65535 bytes ( The number posted by Gail Shaw was wrong).

    Third, the communication protocol of SQL Server (Tabular Data Stream (TDS) ) using Partially Length-Prefixed structure to encode lengths of "MAX" fields , if you mostly store very short strings in those fields, the overhead is significant.

    Our product DB-WAN Accel communicates with SQL Server using TDS protocol.

    Charles Zhang

    http://www.speedydb.com

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

    Any bets on that?

    DECLARE @test-2 VARCHAR(MAX),

    @String VARCHAR(MAX)

    SELECT @test-2 = '',

    @String = '1234567890'

    ;WITH

    cteTally AS

    (--==== Create a Tally CTE from 1 to 10,000

    SELECT TOP (10000)

    ROW_NUMBER() OVER (ORDER BY t1.ID) AS N

    FROM Master.sys.SysColumns t1

    CROSS JOIN Master.sys.SysColumns t2

    )

    SELECT @test-2 = @test-2 + '1234567890'

    FROM cteTally

    SELECT LEN(@Test)

    I have to ask... where did you get such an idea? I'd like to know so I can go straighten them out. 😉

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

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

  • Jeff Moden (2/2/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).

    Any bets on that?

    DECLARE @test-2 VARCHAR(MAX),

    @String VARCHAR(MAX)

    SELECT @test-2 = '',

    @String = '1234567890'

    ;WITH

    cteTally AS

    (--==== Create a Tally CTE from 1 to 10,000

    SELECT TOP (10000)

    ROW_NUMBER() OVER (ORDER BY t1.ID) AS N

    FROM Master.sys.SysColumns t1

    CROSS JOIN Master.sys.SysColumns t2

    )

    SELECT @test-2 = @test-2 + '1234567890'

    FROM cteTally

    SELECT LEN(@Test)

    I have to ask... where did you get such an idea? I'd like to know so I can go straighten them out. 😉

    And now, direct from Book Online for viewing pleasure!

    varchar [ ( n | max ) ]

    Variable-length, non-Unicode character data. n can be a value from 1 through 8,000. max indicates that the maximum storage size is 2^31-1 bytes. The storage size is the actual length of data entered + 2 bytes. The data entered can be 0 characters in length. The SQL-2003 synonyms for varchar are char varying or character varying.

    And

    nvarchar [ ( n | max ) ]

    Variable-length Unicode character data. n can be a value from 1 through 4,000. max indicates that the maximum storage size is 2^31-1 bytes. The storage size, in bytes, is two times the number of characters entered + 2 bytes. The data entered can be 0 characters in length. The SQL-2003 synonyms for nvarchar are national char varying and national character varying.

    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!

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

    Really? What makes you right and Books Online wrong?

    Or is this just an excuse for you to advertise your product?

    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
  • Thank you everyone.

    "I Love Walking In The Rain So No One Can See Me Crying ! " ~ Charlie Chaplin

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

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

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

  • Ooh, I think I'm going to watch this one.

    Max

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

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

  • 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

  • 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

Viewing 15 posts - 1 through 15 (of 57 total)

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