SQL Server: Incorrect decimal column's scale may mean lost money
I have been profiling my codes and my scripts trying to debug why I have lost lots of .50 cents every transaction, given I always use the decimal datatype when computing currency values. The business logic layer persists correct values and profiler shows result with its fractional part being passed to database, but after committing the transaction, all .50 cents are rounded off.
568.50 becomes
599.
Reviewing my schema, i noticed that i have set the datatype of some column's scale to 0 (decimal(10,0)), I may have mistakenly set it. Making it decimal(10,2), fix the issue.
What is a scale btw,
The maximum number of decimal digits that can be stored to the right of the
decimal point. Scale must be a value from 0 through
p. Scale can be
specified only if precision is specified. The default scale is 0; therefore, 0
<=
s <=
p. Maximum storage sizes vary, based on the
precision. -
SQL BOL HTH