What's the Length?

  • Comments posted to this topic are about the item What's the Length?

  • Interesting question but an easy one for me only because I remembered that the default value of the max length of a string is 4000. I have to admit that I needed 5 minutes to be sure of the correct value ( 3996 ) in this case.

    The solution you provided is interesting and useful at least according to me. I am sure I would need 15 minutes to find it as I have not used this function since the release of SQL Server 2008 a long time ago ... ( pure T-SQL is not my preferred topic , but I will do some tests in the next days ).

  • This was removed by the editor as SPAM

  • Great question to start the week. Thanks!

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • The MAX lengths will get you every time when it comes to lengths and joining to them. Interesting question.

  • I would actually change the variable definition.


    @max-2 AS NVARCHAR(MAX) = ''

    , @Var AS NVARCHAR(MAX) = 'ABCabc123';


    @max-2 = REPLICATE(@Var, 445);


    CASE WHEN LEN(@Max) = LEN(@Var) * 445 THEN 'Simple math'

    ELSE 'It''s complicated'


    This is a common problem when creating long dynamic sql strings. I hope that people learn from this question.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • Fun questions, thanks Umer Akram.

    [font="Tahoma"]Personal blog relating fishing to database administration:[/font]

    [font="Comic Sans MS"]https://davegugg.wordpress.com[/url]/[/font]

  • Interesting question. I learned something, b/c I don't use REPLICATE() for long strings.

    The explanation from BOL is quite specific:

    If string_expression is not of type varchar(max) or nvarchar(max), REPLICATE truncates the return value at 8,000 bytes. To return values greater than 8,000 bytes, string_expression must be explicitly cast to the appropriate large-value data type.


  • A really interesting question , thanks Umer . But just simple complicated? 😉


    @max-2 AS NVARCHAR(MAX) = ''

    , @Var AS VARCHAR(4000) = 'ABCabc123';-- Varchar instead Nvarchar --

    SET @max-2 = REPLICATE(@Var, 445);

    SELECT LEN(@Max) as LenMax, LEN(@Var) * 445 as LenVar,

    DATALENGTH(@Max) as DataLenMax, DATALENGTH(@Var) * 445 as DataLenVar;

    Ref.: https://msdn.microsoft.com/en-us/library/ms174383.aspx

  • Nice question, thanks.

Viewing 10 posts - 1 through 9 (of 9 total)

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