What's the Length?

  • twin.devil


    Points: 22208

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

  • patricklambin

    SSCrazy Eights

    Points: 9964

    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

  • Koen Verbeeck

    SSC Guru

    Points: 258985

    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

  • Ed Wagner

    SSC Guru

    Points: 286988

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

  • Luis Cazares

    SSC Guru

    Points: 183645

    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
  • david.gugg


    Points: 5696

    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]

  • Rich Mechaber


    Points: 10935

    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.


  • George Vobr

    SSCrazy Eights

    Points: 9272

    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

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    Nice question, thanks.

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

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