REPLICATE - 1

  • Bangla

    Hall of Fame

    Points: 3137

    Nice question....

  • Carlo Romagnano

    SSC-Insane

    Points: 22008

    crussell-931424 (5/1/2013)


    Hugo Kornelis (5/1/2013)


    Raghavendra Mudugal (5/1/2013)


    sorry; I have hard time in understanding/realizing the missing piece here.

    - Replicate is replicating as needed (the expression is 5 chars and and @S is also 5 so no changes in the @S in the SET level0

    - as the @S is set V5, so the datalength is 5 of the S as-well in the table

    can any one shed some light please?

    I think Ron expected the ANSI_WARNINGS setting to cause errors. Either because of the implicit conversion of 12.5 to integer, or because of the string truncation.

    However ...

    1. Rounding numerical data is not an overflow condition; AFAIK this does not raise an error regardless of ANSI_WARNINGS setting;

    2. String truncation does cause an error to occur with ANSI_WARNINGS on - but only if it occurs while inserting or updating, not when assigning to a variable. This is mentioned explicitly in the Books Online article (http://msdn.microsoft.com/en-us/library/ms190368.aspx).

    If you increase the length of @S to 11 characters or more, the effect of ANSI_WARNINGS does kick in. An error is returned during the INSERT statement, and the final SELECT returns nothing at all. (Since the INSERT errored, no row was inserted, so there is nothing to return).

    Thanks Hugo, that makes sense. I was having a hard time understanding why someone would think there was a problem here.

    +1

  • SQLRNNR

    SSC Guru

    Points: 281252

    emanuel ionescu (5/1/2013)


    Very well put, Hugo!

    +1

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • Ken Wymore

    SSCoach

    Points: 16642

    Interesting question and nice insight from Hugo. Thanks to all!

  • mtassin

    SSC-Insane

    Points: 23099

    I think we should give Hugo bonus points for another awesome explanation...

    Good question no matter what, and an even more awesome explanation from Hugo



    --Mark Tassin
    MCITP - SQL Server DBA
    Proud member of the Anti-RBAR alliance.
    For help with Performance click this link[/url]
    For tips on how to post your problems[/url]

  • Koen Verbeeck

    SSC Guru

    Points: 258965

    Thanks for the question, and Hugo, thanks for the explanation 🙂

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

  • Anipaul

    SSC-Insane

    Points: 24681

    mtassin (5/3/2013)


    I think we should give Hugo bonus points for another awesome explanation...

    Good question no matter what, and an even more awesome explanation from Hugo

    Yes, I agree......

  • manik_anu

    SSCrazy

    Points: 2367

    nice question....

    Manik
    You cannot get to the top by sitting on your bottom.

  • sqlnaive

    SSCoach

    Points: 17435

    Very good question and excellent explanation. 🙂

  • Dscheypie

    SSCommitted

    Points: 1545

    Hugo Kornelis (5/1/2013)

    I think Ron expected the ANSI_WARNINGS setting to cause errors. Either because of the implicit conversion of 12.5 to integer, or because of the string truncation.

    However ...

    1. Rounding numerical data is not an overflow condition; AFAIK this does not raise an error regardless of ANSI_WARNINGS setting;

    2. String truncation does cause an error to occur with ANSI_WARNINGS on - but only if it occurs while inserting or updating, not when assigning to a variable. This is mentioned explicitly in the Books Online article (http://msdn.microsoft.com/en-us/library/ms190368.aspx).

    If you increase the length of @S to 11 characters or more, the effect of ANSI_WARNINGS does kick in. An error is returned during the INSERT statement, and the final SELECT returns nothing at all. (Since the INSERT errored, no row was inserted, so there is nothing to return).

    Took me a while to understand, what you wrote there, Hugo, but:

    "[...] string too large for database column [...]" in the article http://msdn.microsoft.com/en-us/library/ms190368.aspx made it clear after all. Thanx for your explanation, Hugo, and also thanx to the author of the QotD!

    ________________________________________________________
    If you set out to do something, something else must be done first.

Viewing 10 posts - 16 through 25 (of 25 total)

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