VarChar(max)?!?

  • Comments posted to this topic are about the item VarChar(max)?!?



    PeteK
    I have CDO. It's like OCD but all the letters are in alphabetical order... as they should be.

  • I thought the explanation was really lacking something ... like explaining

    The REPLICATE function only returns 8000 characters (defaults to VARCHAR(8000)) if the character value to be replicated isn't explicitly CAST as VARCHAR(MAX). As both REPLICATE statements are internally CAST as VARCHAR(8000) fields, the concatenation of the two VARCHAR(8000) fields yields another VARCHAR(8000) field before assigning the value to the @STR field, hence the results is 8000, not 16000 as you might expect.

    To try and make that clearer ...

    DECLARE @STR VARCHAR(MAX);

    -- The Example from the QOTD

    SET @STR=REPLICATE('*',10000) + REPLICATE('*',10000);

    PRINT LEN(@Str);

    -- Explicitly CAST the Replicated character as type VARCHAR(MAX)

    SET @STR=REPLICATE(CAST('*' AS VARCHAR(MAX)),10000) + REPLICATE(CAST('*' AS VARCHAR(MAX)),10000);

    PRINT LEN(@Str);

    -- Explicitly CAST the results from REPLICATE statements as VARCHAR(MAX), without casting the Replicated character as VARCHAR(MAX)

    SET @STR=CAST(REPLICATE('*',10000) AS VARCHAR(MAX)) + CAST(REPLICATE('*',10000) AS VARCHAR(MAX));

    PRINT LEN(@Str);

    Gives the results:

    8000

    20000

    16000

  • Yup... i got the same results..

    "Keep Trying"

  • Nice question.

  • Nice...

    Atif Sheikh

    ----------------------------------------------------------------------------------------------------------------------------------------------------------------------
    Sometimes, winning is not an issue but trying.
    You can check my BLOG
    [font="Arial Black"]here[/font][/url][/right]

  • I spotted the implicit conversion and was about to go into the details on it, but then realized Simon had already covered it.

    Good question. Understanding implicit conversions is a critical skill for anyone dealing with code.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • Simon, thanks for the explanation. Implicit conversions involving varchar (max) have bitten me a couple of times already. Nice question.

  • Nice question. This hasn't burned my yet and this type of question may mean it never does! Thanks.

  • Even though the answer was obvious, and even though I had QA open at the time, I went ahead and "Took the Bait". What's 1 lousy point.

    Good QotD. Learnt sumpin.

    Tom Garth
    Vertical Solutions[/url]

    "There are three kinds of men. The one that learns by reading. The few who learn by observation. The rest of them have to pee on the electric fence for themselves." -- Will Rogers
  • this makes varchar(max) a special type ... necessitating special handling aka "workaround" for a bug, maybe ??

  • Glad to see a clearer explanation from Simon, had a better understanding after that.

    Regards,

    Phil

  • vlad (8/11/2008)


    this makes varchar(max) a special type ... necessitating special handling aka "workaround" for a bug, maybe ??

    Nope. Implicit conversion can be a problem on just about any data type. Elimination of implicit conversion would be the only way to handle that, and implicit conversion has too many good uses to get rid of it because of people who don't learn how to handle it correctly.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • Nope. Implicit conversion can be a problem on just about any data type. Elimination of implicit conversion would be the only way to handle that, and implicit conversion has too many good uses to get rid of it because of people who don't learn how to handle it correctly.

    - GSquared

    Despite the fact that implicit conversion maybe good or bad, varchar(max) is a not the same type as varchar(N), 0<=N<=8000 .. something to keep in mind!

  • Hi Simon,

    Thank for the explanation of the question.

  • vlad (8/12/2008)


    Nope. Implicit conversion can be a problem on just about any data type. Elimination of implicit conversion would be the only way to handle that, and implicit conversion has too many good uses to get rid of it because of people who don't learn how to handle it correctly.

    - GSquared

    Despite the fact that implicit conversion maybe good or bad, varchar(max) is a not the same type as varchar(N), 0<=N<=8000 .. something to keep in mind!

    Exactly.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Viewing 15 posts - 1 through 15 (of 17 total)

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