REPLICATE - 1

  • 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]

  • Thanks for question.

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

  • 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

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

  • 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/

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

  • Very well put, Hugo!

  • 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

  • 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

  • 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] 😉

  • vinu512 (5/1/2013)


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

    +1



    Everything is awesome!

  • vinu512 (5/1/2013)


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

    +1

  • Interesting, very interesting... Thanks, Ron!

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

    Not all gray hairs are Dinosaurs!

  • 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