Difference between varchar(max) and varchar(8000)

  • Cyberspy

    SSC Eights!

    Points: 860

    Hi,

    I know this post is now 5 1/2 years old but, as Jeff says, it's fun... :Whistling:

    I found this post looking for an answer to the original question, and now know far more about it than I expected to, which is great.

    Why am I posting though?

    I had a look at Charesz's web site (http://www.speedydb.com ) out of curiosity and it seems like common sense has won the day.

    It's now just a generic holding page: seems like SpeedyDB is no more.

    SQL Server Central rocks! 😀

  • dennisp

    SSC Eights!

    Points: 810

    Hey Gail, my memory is failing me.

    What are the reasons again to put varchar(max)'s at the end of the table.

    I remember Kimberly and/or Kalen talking about this years ago and just don't recall.

    Dennis Parks
    MCSE, MCDBA, MCSD, MCAD, MCTS

  • kevin dockerty

    Valued Member

    Points: 64

    Varchar(MAX) is 2GB for sure.

    Kevin Dockerty
    SQL Developer, DBA and website owner
    t-sql developer forum

  • parentd

    Valued Member

    Points: 52

    My experience with Jeff's SQL Code is - I copied and pasted it into my SQL 2008 query window and executed it.  I got a value of 100,000 characters for the LEN(@Text).  I then got the printout of @Text variable in the Results pane and - - - it did not print anything out!   I have SQL Code that dynamically creates a very long SQL Statement and I can do a PRINT @strSQLCode but it only prints out the first 8,000 characters!
    So, you are both right in your own way.  The problem I am having is I need to create AND EXECUTE a dynamic SQL Statement that is longer than 8,000 characters.  If I can create it but it does not run, there is no benefit for having this very long text string in one VARCHAR(MAX) variable!

  • Lynn Pettis

    SSC Guru

    Points: 442144

    parentd - Wednesday, April 11, 2018 8:38 AM

    My experience with Jeff's SQL Code is - I copied and pasted it into my SQL 2008 query window and executed it.  I got a value of 100,000 characters for the LEN(@Text).  I then got the printout of @Text variable in the Results pane and - - - it did not print anything out!   I have SQL Code that dynamically creates a very long SQL Statement and I can do a PRINT @strSQLCode but it only prints out the first 8,000 characters!
    So, you are both right in your own way.  The problem I am having is I need to create AND EXECUTE a dynamic SQL Statement that is longer than 8,000 characters.  If I can create it but it does not run, there is no benefit for having this very long text string in one VARCHAR(MAX) variable!

    If you are using sp_executesql, the variable holding the dynamic SQL will be a NVARCHAR(MAX) and even if you PRINT the contents of the variable you may not see it all, but it will all be there when you execute it.  I write quite a bit of dynamic SQL and some of it is quite longer than 8000 bytes.

    I use this, select cast('<![CDATA[' + @SQLCmd + ']]>' as xml);, to look at the code I am generating.

  • parentd

    Valued Member

    Points: 52

    Lynn,

    Thanks for that info.  I will definitely try using it.

  • Saurabh Dwivedy

    SSCommitted

    Points: 1881

    Here are two pieces of code I ran today to check the maximum number of characters that a VARCHAR(MAX) can accommodate.
    --===== Query 1
    DECLARE @Val INT, @Var VARCHAR(MAX), @Text VARCHAR(MAX)
         SET @Val = 100000
         SET @Var = ''
         SET @Text = '1234'

    SELECT @Var = @Var + @Text
      FROM
      (SELECT TOP(@Val) N
       FROM dbo.Tally) D
    --==== Check Output
    SELECT LEN(@Var) AS Size1

    --===== Query 2
    DECLARE @X VARCHAR(MAX)
         SET @X = REPLICATE('x', 10000)
    --==== Check Output
    SELECT LEN(@X) AS Size2

    Here are the outputs for Query1 and Query 2 respectively


    Both variables @Var and @X are declared VARCHAR(MAX). But when I populate 10000 characters worth of 'x' in @X using replicate and check its output via LEN(@X) it returns the value capped at 8000. Whereas the variable @Var accommodates as many as 400000 characters! I am not sure why this is so? Can someone help me understand why with REPLICATE it shows the length as 8000 but when I run it using the tally table approach it accommodates a size of 400000 characters?

    Many thanks

    Saurabh Dwivedy
    ___________________________________________________________

    My Blog: http://tinyurl.com/dwivedys

    For better, quicker answers, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537

    Be Happy!
  • John Mitchell-245523

    SSC Guru

    Points: 148319

    Saurabh Dwivedy - Thursday, April 12, 2018 4:40 AM

    Here are two pieces of code I ran today to check the maximum number of characters that a VARCHAR(MAX) can accommodate.
    --===== Query 1
    DECLARE @Val INT, @Var VARCHAR(MAX), @Text VARCHAR(MAX)
         SET @Val = 100000
         SET @Var = ''
         SET @Text = '1234'

    SELECT @Var = @Var + @Text
      FROM
      (SELECT TOP(@Val) N
       FROM dbo.Tally) D
    --==== Check Output
    SELECT LEN(@Var) AS Size1

    --===== Query 2
    DECLARE @X VARCHAR(MAX)
         SET @X = REPLICATE('x', 10000)
    --==== Check Output
    SELECT LEN(@X) AS Size2

    Here are the outputs for Query1 and Query 2 respectively


    Both variables @Var and @X are declared VARCHAR(MAX). But when I populate 10000 characters worth of 'x' in @X using replicate and check its output via LEN(@X) it returns the value capped at 8000. Whereas the variable @Var accommodates as many as 400000 characters! I am not sure why this is so? Can someone help me understand why with REPLICATE it shows the length as 8000 but when I run it using the tally table approach it accommodates a size of 400000 characters?

    Many thanks

    --===== Query 2
    DECLARE @X VARCHAR(MAX)
      SET @X = REPLICATE(CAST('x' as varchar(max)), 10000)
    --==== Check Output
    SELECT LEN(@X) AS Size2

    John

  • Saurabh Dwivedy

    SSCommitted

    Points: 1881

    John Mitchell-245523 - Thursday, April 12, 2018 4:51 AM

    Saurabh Dwivedy - Thursday, April 12, 2018 4:40 AM

    Here are two pieces of code I ran today to check the maximum number of characters that a VARCHAR(MAX) can accommodate.
    --===== Query 1
    DECLARE @Val INT, @Var VARCHAR(MAX), @Text VARCHAR(MAX)
         SET @Val = 100000
         SET @Var = ''
         SET @Text = '1234'

    SELECT @Var = @Var + @Text
      FROM
      (SELECT TOP(@Val) N
       FROM dbo.Tally) D
    --==== Check Output
    SELECT LEN(@Var) AS Size1

    --===== Query 2
    DECLARE @X VARCHAR(MAX)
         SET @X = REPLICATE('x', 10000)
    --==== Check Output
    SELECT LEN(@X) AS Size2

    Here are the outputs for Query1 and Query 2 respectively


    Both variables @Var and @X are declared VARCHAR(MAX). But when I populate 10000 characters worth of 'x' in @X using replicate and check its output via LEN(@X) it returns the value capped at 8000. Whereas the variable @Var accommodates as many as 400000 characters! I am not sure why this is so? Can someone help me understand why with REPLICATE it shows the length as 8000 but when I run it using the tally table approach it accommodates a size of 400000 characters?

    Many thanks

    --===== Query 2
    DECLARE @X VARCHAR(MAX)
      SET @X = REPLICATE(CAST('x' as varchar(max)), 10000)
    --==== Check Output
    SELECT LEN(@X) AS Size2

    John

    Wow! Thanks much. Never realized it needed a CAST!

    Saurabh Dwivedy
    ___________________________________________________________

    My Blog: http://tinyurl.com/dwivedys

    For better, quicker answers, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537

    Be Happy!
  • Lynn Pettis

    SSC Guru

    Points: 442144

    Saurabh Dwivedy - Thursday, April 12, 2018 10:56 AM

    John Mitchell-245523 - Thursday, April 12, 2018 4:51 AM

    Saurabh Dwivedy - Thursday, April 12, 2018 4:40 AM

    Here are two pieces of code I ran today to check the maximum number of characters that a VARCHAR(MAX) can accommodate.
    --===== Query 1
    DECLARE @Val INT, @Var VARCHAR(MAX), @Text VARCHAR(MAX)
         SET @Val = 100000
         SET @Var = ''
         SET @Text = '1234'

    SELECT @Var = @Var + @Text
      FROM
      (SELECT TOP(@Val) N
       FROM dbo.Tally) D
    --==== Check Output
    SELECT LEN(@Var) AS Size1

    --===== Query 2
    DECLARE @X VARCHAR(MAX)
         SET @X = REPLICATE('x', 10000)
    --==== Check Output
    SELECT LEN(@X) AS Size2

    Here are the outputs for Query1 and Query 2 respectively


    Both variables @Var and @X are declared VARCHAR(MAX). But when I populate 10000 characters worth of 'x' in @X using replicate and check its output via LEN(@X) it returns the value capped at 8000. Whereas the variable @Var accommodates as many as 400000 characters! I am not sure why this is so? Can someone help me understand why with REPLICATE it shows the length as 8000 but when I run it using the tally table approach it accommodates a size of 400000 characters?

    Many thanks

    --===== Query 2
    DECLARE @X VARCHAR(MAX)
      SET @X = REPLICATE(CAST('x' as varchar(max)), 10000)
    --==== Check Output
    SELECT LEN(@X) AS Size2

    John

    Wow! Thanks much. Never realized it needed a CAST!

    Read this to find out why: https://docs.microsoft.com/en-us/sql/t-sql/functions/replicate-transact-sql

  • dennisp

    SSC Eights!

    Points: 810

    Saurabh Dwivedy - Thursday, April 12, 2018 4:40 AM

    Here are two pieces of code I ran today to check the maximum number of characters that a VARCHAR(MAX) can accommodate.
    --===== Query 1
    DECLARE @Val INT, @Var VARCHAR(MAX), @Text VARCHAR(MAX)
         SET @Val = 100000
         SET @Var = ''
         SET @Text = '1234'

    SELECT @Var = @Var + @Text
      FROM
      (SELECT TOP(@Val) N
       FROM dbo.Tally) D
    --==== Check Output
    SELECT LEN(@Var) AS Size1

    --===== Query 2
    DECLARE @X VARCHAR(MAX)
         SET @X = REPLICATE('x', 10000)
    --==== Check Output
    SELECT LEN(@X) AS Size2

    Here are the outputs for Query1 and Query 2 respectively


    Both variables @Var and @X are declared VARCHAR(MAX). But when I populate 10000 characters worth of 'x' in @X using replicate and check its output via LEN(@X) it returns the value capped at 8000. Whereas the variable @Var accommodates as many as 400000 characters! I am not sure why this is so? Can someone help me understand why with REPLICATE it shows the length as 8000 but when I run it using the tally table approach it accommodates a size of 400000 characters?

    Many thanks

    This works too.  You just have to have at least one of the arguments typed as a varchar(max)

    DECLARE @X VARCHAR(MAX)
    SET @X = cast(REPLICATE('x', 5000) as varchar(max)) + REPLICATE('x', 5000)
    --==== Check Output
    SELECT LEN(@X) AS Size2

    Dennis Parks
    MCSE, MCDBA, MCSD, MCAD, MCTS

  • Prometheus112

    SSC-Addicted

    Points: 423

    dennisp - Monday, June 15, 2015 5:39 PM

    Hey Gail, my memory is failing me.What are the reasons again to put varchar(max)'s at the end of the table.I remember Kimberly and/or Kalen talking about this years ago and just don't recall.

    I know this is super old (but most entertaining thread I have seen in a while) - did anyone happen to catch Dennis's question?  I was wondering if there were any catches on this and what peoples opinions are on it.

Viewing 12 posts - 46 through 57 (of 57 total)

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