Constant Data Types

Question

Constant Data Types

Kenneth.Fisher

SSC-Insane

Points: 20396
More actions
October 2, 2016 at 10:29 pm

#330970

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]

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

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

simon.barnes Old Hand Points: 370 More actions · Answer 1

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 More actions · Answer 2

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: 1089 More actions · Answer 3

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.

The three biggest mistakes in life...thinking that power = freedom, sex = love, and data = information.

akljfhnlaflkj SSC Guru Points: 76202 More actions · Answer 4

akljfhnlaflkj

SSC Guru

Points: 76202

October 3, 2016 at 11:42 am

#1904497

Nice article. Didn't know about that.

Bill Talada SSChampion Points: 11954 More actions · Answer 5

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: 4168 More actions · Answer 6

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: 3668 More actions · Answer 7

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: 4168 More actions · Answer 8

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.