Post reply

REPLICATE - 1

  • April 30, 2013 at 8:25 pm

    #274779

    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]

  • April 30, 2013 at 11:22 pm

    #1611464

    Thanks for question.

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

  • May 1, 2013 at 12:16 am

    #1611472

    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

  • May 1, 2013 at 1:03 am

    #1611477

    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-2 is also 5 so no changes in the @s-2 in the SET level0

    - as the @s-2 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.

  • May 1, 2013 at 2:03 am

    #1611485

    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-2 is also 5 so no changes in the @s-2 in the SET level0

    - as the @s-2 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-2 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/

  • May 1, 2013 at 2:22 am

    #1611487

    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.

  • May 1, 2013 at 2:37 am

    #1611492

    Very well put, Hugo!

  • May 1, 2013 at 2:50 am

    #1611498

    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

  • May 1, 2013 at 3:25 am

    #1611506

    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

  • May 1, 2013 at 5:51 am

    #1611522

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

  • May 1, 2013 at 7:07 am

    #1611541

    vinu512 (5/1/2013)

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

    +1


    Everything is awesome!

  • May 1, 2013 at 7:58 am

    #1611560

    vinu512 (5/1/2013)

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

    +1

  • May 1, 2013 at 9:06 am

    #1611586

    Interesting, very interesting... Thanks, Ron!

  • May 1, 2013 at 9:34 am

    #1611610

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

    Not all gray hairs are Dinosaurs!

  • May 1, 2013 at 11:09 pm

    #1611789

    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 24 total)

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