none
VARCHAR MAX LENGTH - SQL 2005 ?

    Question

  • Hi All,
    I wanted to know the maximum length of the VARCHAR field with SQL 2005.  I believe the maximum value is VARCHAR(8000).  Does this value increase above 8000, if some service pack is added to SQL Server 2005 ?

    DECLARE @STR VARCHAR(8000)

    Can this become like VARChAR(10000) ? or something greater than 8000 ?
    Monday, March 02, 2009 8:43 PM

Answers

All replies

  • MS Techie said:

    Hi All,
    I wanted to know the maximum length of the VARCHAR field with SQL 2005.  I believe the maximum value is VARCHAR(8000).  Does this value increase above 8000, if some service pack is added to SQL Server 2005 ?

    DECLARE @STR VARCHAR(8000)

    Can this become like VARChAR(10000) ? or something greater than 8000 ?


    2^31-1 bytes


    Example:

    DECLARE @s VARCHAR(MAX);

    SET @s = REVERSE(CAST(SPACE(8000) AS VARCHAR(MAX)) + 'a');

    SELECT @s, DATALENGTH(@s);
    GO


    AMB
    Monday, March 02, 2009 9:02 PM
  • So if you put

    DECLARE @s VARCHAR(MAX);

    SET @s = REVERSE(CAST(SPACE(90000) AS VARCHAR(MAX)) + 'a'
    );

    SELECT @s, DATALENGTH(@s
    );
    GO


    Is  the result going to be 90,001?
    Monday, March 02, 2009 9:17 PM
  • No, the result is 8001, because type from SPACE function is char. But try below:

    DECLARE @s VARCHAR(MAX);  
     
    SET @s = REPLICATE(CAST('c' AS varchar(max)), 10000) + 'a' 
     
    SELECT DATALENGTH(@s);  
    GO 

    Tibor Karaszi
    • Marked as answer by MS Techie Tuesday, March 03, 2009 8:34 AM
    Monday, March 02, 2009 10:12 PM
  • As Hunchback and Tibor have already pointed, you should cast the value to VARCHAR(MAX) when passing to any of the string functions of SQL Server. If you do not cast them to VARCHAR(MAX), SQL Server will assume VARCHAR(8000) and your value will be truncated.

    I would recomment reading the following article: http://blog.beyondrelational.com/2008/01/varcharnvarchar-n-vs-max.html

    http://blog.beyondrelational.com/
    • Marked as answer by MS Techie Tuesday, March 03, 2009 8:34 AM
    Monday, March 02, 2009 10:55 PM