Constant Data Types

  • Kenneth.Fisher

    SSCoach

    Points: 19587

    Comments posted to this topic are about the item Constant Data Types

    Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]

  • simon.barnes

    Old Hand

    Points: 370

    I use [font="Courier New"]sql_variant_property()[/font] when analysing literals, but you have to use it three times to get all the information you need about decimal and numeric types (assuming you're making a distinction between decimal and numeric; otherwise twice). SQL Prompt helps 🙂

  • xanthos

    SSCrazy

    Points: 2510

    The following page shows how the result precision and result scale will be calculated.

    https://msdn.microsoft.com/en-us/library/ms190476.aspx

    It makes sense in the 2560/1024.0 example if 2560 is converted to numeric(5, 1) first

  • IMHO

    Ten Centuries

    Points: 1006

    Great article! This can be one of the biggest 'gotchas'! I've had production code go bonkers using round before because I didn't understand why Round() didn't just work the way I expected it to. My solution to this is to cast the value before rounding it.

    SELECT ROUND(cast(.07 as numeric(2,1)),0)

    Not sure if there might be problems with this as well, though.

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    Nice article. Didn't know about that.

  • Bill Talada

    SSChampion

    Points: 11956

    Freaky code! I simply never specify constant strings nor values inline. And I heavily use cast when something is unknown. I would implement a constant with something like

    DECLARE @const_dec2 decimal(18,2) = '123.45';

    SELECT @const_dec2, 123.456;

  • Toby Harman

    SSCarpal Tunnel

    Points: 4128

    Thumbs up for this link posted by xanthos.

    https://msdn.microsoft.com/en-us/library/ms190476.aspx

    Some time ago I was working with financial services organisation and yield calculations were an important part. Getting these decimal values and sizes correct became very important as it can very quickly go beyond the maximum size and start truncating decimal points in the calculation.

    The last paragraph on that page is crucial to note

    * The result precision and scale have an absolute maximum of 38. When a result precision is greater than 38, the corresponding scale is reduced to prevent the integral part of a result from being truncated.

    E.g. (x * y) / z. If all 3 of those are defined as DECIMAL(9, 5) then you will just avoid a result that is truncated.

  • thisisfutile

    Hall of Fame

    Points: 3490

    Toby Harman (10/3/2016)


    Thumbs up for this link posted by xanthos.

    https://msdn.microsoft.com/en-us/library/ms190476.aspx

    .....

    E.g. (x * y) / z. If all 3 of those are defined as DECIMAL(9, 5) then you will just avoid a result that is truncated.

    I agree, thanks to Xanthos for posting the link and I appreciate you, Toby for posting an example. It challenged me to take a closer look at precision/scale and how the results are considered. Although, I found myself wrestling with arithmetic overflows so frequently I didn't get far with using actual variables in SQL code, I was able to calculate (on "paper") that (34,20) would be the result's precision/scale in your puzzle. Yes?

  • Toby Harman

    SSCarpal Tunnel

    Points: 4128

    So 3 variables x, y and z all defined as DECIMAL (9, 5) (i.e 99,999.99999)

    What is the resultant datatype from this calulation

    (x * y) / z

    Starting with the multiplication inside the brackets

    Precision = p1 + p2 + 1 = 9 + 9 + 1 = 19

    Scale = s1 + s2 = 10

    So now we have a decimal (19, 10) divided by a decimal (9, 5)

    Precision = p1 - s1 + s2 + max(6, s1 + p2 + 1) = 19 - 5 + 5 + max(6, 5 + 9 + 1) = 19 + 15 = 34

    Scale = max(6, s1 + p2 + 1) = 15

    So no, I believe it will be a DECIMAL (34, 15)

    Make the original variables decimal (10, 6) to add more precision and you come out with a decimal (38, 17).

    One more on the precision and it will overflow, and would not be able to reverse the calculation accurately.

Viewing 9 posts - 1 through 9 (of 9 total)

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