Difference between varchar(max) and varchar(8000)

• Cyberspy

  SSC Eights!

  Points: 920

  More actions

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

• dennisp

  SSC Eights!

  Points: 811

  More actions

  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

• kevin dockerty

  Valued Member

  Points: 64

  More actions

  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

• parentd

  Valued Member

  Points: 52

  More actions

  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!

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  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.

• parentd

  Valued Member

  Points: 52

  More actions

  April 11, 2018 at 11:50 am

  #1986430

  Lynn,

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

• Saurabh Dwivedy

  SSCommitted

  Points: 1887

  More actions

  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!

• John Mitchell-245523

  SSC Guru

  Points: 148809

  More actions

  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

• Saurabh Dwivedy

  SSCommitted

  Points: 1887

  More actions

  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!

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  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

• dennisp

  SSC Eights!

  Points: 811

  More actions

  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

• Prometheus112

  Mr or Mrs. 500

  Points: 503

  More actions

  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.