Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

REPLICATE - 1 Expand / Collapse
Author
Message
Posted Wednesday, May 1, 2013 6:53 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 8:39 AM
Points: 2,935, Visits: 239
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.
Post #1448399
Posted Wednesday, May 1, 2013 7:07 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 9:01 AM
Points: 1,878, Visits: 1,414
vinu512 (5/1/2013)
Good Question Ron....and thanks for the explanation Hugo.


+1




Everything is awesome!
Post #1448405
Posted Wednesday, May 1, 2013 7:58 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 2:15 PM
Points: 317, Visits: 694
vinu512 (5/1/2013)
Good Question Ron....and thanks for the explanation Hugo.


+1
Post #1448426
Posted Wednesday, May 1, 2013 9:06 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Monday, September 15, 2014 11:54 AM
Points: 4,427, Visits: 3,423
Interesting, very interesting... Thanks, Ron!
Post #1448460
Posted Wednesday, May 1, 2013 9:34 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 5:34 PM
Points: 2,341, Visits: 1,401
+1 Thanks for your contribution to man of us learning something in this question.

Not all gray hairs are Dinosaurs!
Post #1448486
Posted Wednesday, May 1, 2013 11:09 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 3:27 AM
Points: 1,893, Visits: 2,329
vinu512 (5/1/2013)
Good Question Ron....and thanks for the explanation Hugo.

+1



_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1448689
Posted Thursday, May 2, 2013 12:26 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, September 10, 2014 5:38 AM
Points: 823, Visits: 154
Nice question....
Post #1448704
Posted Thursday, May 2, 2013 1:14 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 12:53 AM
Points: 2,529, Visits: 2,402
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
Post #1448707
Posted Thursday, May 2, 2013 11:07 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 10:58 PM
Points: 21,755, Visits: 15,461
emanuel ionescu (5/1/2013)
Very well put, Hugo!


+1




Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1448925
Posted Thursday, May 2, 2013 11:50 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 10:57 AM
Points: 3,323, Visits: 1,988
Interesting question and nice insight from Hugo. Thanks to all!
Post #1448932
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse