Difference between varchar(max) and varchar(8000)

  • jchandramouli

    SSCommitted

    Points: 1772

    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

  • Gail Shaw

    SSC Guru

    Points: 1004446

    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
  • John H Marsh

    SSChampion

    Points: 11671

    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

  • SwePeso

    SSC-Dedicated

    Points: 39693

    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"

  • charlesz

    Old Hand

    Points: 381

    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

  • Jeff Moden

    SSC Guru

    Points: 994844

    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 VARCHAR(MAX),

    @String VARCHAR(MAX)

    SELECT @test = '',

    @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 = @test + '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.
    "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)

  • Lynn Pettis

    SSC Guru

    Points: 442144

    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 VARCHAR(MAX),

    @String VARCHAR(MAX)

    SELECT @test = '',

    @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 = @test + '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!

  • Gail Shaw

    SSC Guru

    Points: 1004446

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

    SSCommitted

    Points: 1772

    Thank you everyone.

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

  • Jeff Moden

    SSC Guru

    Points: 994844

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

  • Max-146500

    SSCarpal Tunnel

    Points: 4691

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

    Max

  • charlesz

    Old Hand

    Points: 381

    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

    SSC Guru

    Points: 64818

    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

    SSC Guru

    Points: 96590

    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

    SSC Guru

    Points: 96590

    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