Post reply

Difference between varchar(max) and varchar(8000)

  • October 2, 2014 at 3:32 am

    #1749635

    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! 😀

  • June 15, 2015 at 5:39 pm

    #1805616

    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

  • December 29, 2015 at 4:49 am

    #1848420

    Varchar(MAX) is 2GB for sure.

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

  • April 11, 2018 at 8:38 am

    #1986363

    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!

  • April 11, 2018 at 11:08 am

    #1986418

    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.

  • April 11, 2018 at 11:50 am

    #1986430

    Lynn,

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

  • April 12, 2018 at 4:40 am

    #1986538

    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!

  • April 12, 2018 at 4:51 am

    #1986540

    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

  • April 12, 2018 at 10:56 am

    #1986636

    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!

  • April 12, 2018 at 11:11 am

    #1986638

    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

  • April 12, 2018 at 12:40 pm

    #1986662

    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

  • July 27, 2018 at 10:05 am

    #1999271

    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 56 (of 56 total)

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

Cookies on SQLServerCentral

This website stores cookies on your computer.

These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.

To find out more about the cookies we use, see our Privacy Policy