REPLICATE - 1

  • Ron McCullough

    SSC Guru

    Points: 63877

    Comments posted to this topic are about the item REPLICATE - 1

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • Vinay Kumar

    SSCertifiable

    Points: 6098

    Thanks for question.

    Thanks
    Vinay Kumar
    -----------------------------------------------------------------
    Keep Learning - Keep Growing !!!

  • Lokesh Vij

    SSChampion

    Points: 10836

    Thanks Ron an interesting question. I never thought of trying and testing that option 🙂

    ~ Lokesh Vij


    Guidelines for quicker answers on T-SQL question[/url]
    Guidelines for answers on Performance questions

    Link to my Blog Post --> www.SQLPathy.com[/url]

    Follow me @Twitter

  • Raghavendra Mudugal

    SSChampion

    Points: 10658

    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?

    ww; Raghu
    --
    The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.

  • Hugo Kornelis

    SSC Guru

    Points: 64685

    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).


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • Raghavendra Mudugal

    SSChampion

    Points: 10658

    Thank you, Hugo. Glad I am on the same page. (but I still don't get the catch; possibly it may be what you have mentioned in the first line.)

    ww; Raghu
    --
    The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.

  • emanuel ionescu

    SSC Eights!

    Points: 883

    Very well put, Hugo!

  • TomThomson

    SSC Guru

    Points: 104773

    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.[/QUOTE]

    As the explanation refers to the BOL page on REPLICATE, and not to the page on SET ANSI_WARNINGS, I suspect it was the former.

    However ...

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

    [/QUOTE]

    I imagine that was it.

    Despite all that, it is an interesting question. Actually, until I saw the explanation I thought it was a very good question, but unfortunately that explanation will probably confuse quite a few people.

    Tom

  • WWDMark

    Hall of Fame

    Points: 3157

    Interesting QoTD, got me thinking about the conversion of 12.5 and why. Thanks.


    MCITP
    MCTS - E-Business Card
    Twitter: WWDMark

    Try not! Do or do not, there is no try

    email: info@weekendwebdesign.co.uk
    Personal Website: http://markallen.co.uk/
    Business Website: https://www.weekendwebdesign.co.uk

  • vinu512

    SSCoach

    Points: 15729

    Good Question Ron....and thanks for the explanation Hugo.

    Vinu Vijayan

    For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden[/url] 😉

  • Dana Medley

    SSCertifiable

    Points: 6764

    vinu512 (5/1/2013)


    Good Question Ron....and thanks for the explanation Hugo.

    +1



    Everything is awesome!

  • DBA by default

    Mr or Mrs. 500

    Points: 502

    vinu512 (5/1/2013)


    Good Question Ron....and thanks for the explanation Hugo.

    +1

  • Revenant

    SSC-Forever

    Points: 42467

    Interesting, very interesting... Thanks, Ron!

  • Miles Neale

    SSChampion

    Points: 13147

    +1 Thanks for your contribution to man of us learning something in this question.

    Not all gray hairs are Dinosaurs!

  • kapil_kk

    SSC-Insane

    Points: 21316

    vinu512 (5/1/2013)


    Good Question Ron....and thanks for the explanation Hugo.

    +1 :-):-P

    _______________________________________________________________
    To get quick answer follow this link:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

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

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