$$SWYNK$$
Featured Article(s)
How Do You Transition from a Consulting Job Back to Management?
I recently received a question regarding “How do you transition from a consulting job back into management?” This is a great topic, because the answer is useful whether you are moving from a consulting job back into IT, or management; OR moving from any field to a different career.
Featured White Paper(s)
Encryption & Key Management for Microsoft SQL Server 2008
Written by Townsend Security
Simplify encryption and key management on … (read more)
Featured Script
Audit – Stored Procedure Permissions
List who has EXECUTE permissions, by Stored Procedure name or by user name… (read more)
Precision and Conversion
Recently I was reminded of the importance of knowing how data is converted from one type to another. Especially when the data conversion is implicit.
For example, what happens to a number in SQL Server that is a Decimal (7,5) when it is converted to a Decimal (5,3)? By default, using a CAST function from the one to the other, the number will be rounded to the nearest decimal.
What happens when you move to another Database Engine? What happens when you move the logic into Java or Dot net? Then the answer becomes even more obscure.
I found out that Dot Net does not round the same way as SQL Server does using a ROUND function. By Default, Dot Net rounds using a Banker Rounding method which is often statistically more accurate. However, Rounding in Dot Net using the Math.Round method you have to specify not only how many digits to retain, but also MidpointRounding.AwayFromZero if you want to have the same rounding found in SQL Server, or MidPointRounding.ToEven if you wish to have the Banker Rounding Technique.
Note: Per Microsoft, “MidpointRounding.ToEven . If the one’s digit is odd, it is changed to an even digit. Otherwise, it is left unchanged. This behavior follows IEEE Standard 754, section 4. It is sometimes called rounding to nearest, or banker’s rounding. It minimizes rounding errors that result from consistently rounding a midpoint value in a single direction.”
Another thing to consider is the precedence of operations. If you have a complex formula with lots of operations and functions it is more difficult to determine what the final outcome will be when you are relying on the precedence of operations. If you need to have your SQL code produce the same output as your procedural code you may need to specify the precedence in both to assure consistency.
Send an Email to btaylor@sswug.org if you would like to share your comments or experiences on this or any other topic.
Cheers,
Ben