0

I have never worked with currency below in a database. I have done some research, which tells me that you should use the Decimal datatype. Please see the DDL below:

create table dbCurrency (id int not null identity, CurrencyValue decimal (4,2), primary key (id))
insert into dbCurrency (CurrencyValue) values (50);
insert into dbCurrency (CurrencyValue) values (20);
insert into dbCurrency (CurrencyValue) values (10);
insert into dbCurrency (CurrencyValue) values (5);
insert into dbCurrency (CurrencyValue) values (2);
insert into dbCurrency (CurrencyValue) values (1);
insert into dbCurrency (CurrencyValue) values (0.5);
insert into dbCurrency (CurrencyValue) values (0.2);
insert into dbCurrency (CurrencyValue) values (0.1);
insert into dbCurrency (CurrencyValue) values (0.05);
insert into dbCurrency (CurrencyValue) values (0.02);
insert into dbCurrency (CurrencyValue) values (0.01);

This stores all of the UK currencies. Is this the correct way to do it. The reason I ask is because someone I spoke to suggested the data type should be: decimal (5,2) rather than decimal (4,2). He knew I was storing currencies up to £50.

Also, say I have two C# decimals:

decimal first;
decimal second;
decimal third;

say I wanted to divide the first decimal by the second decimal to produce a third decimal. Would the third decimal also be a c# decimal datatype? The first decimal is always larger than the second and the result could be a fraction. The first and second decimals are monetary values.

w0051977
  • 15,099
  • 32
  • 152
  • 329

2 Answers2

0

You have chosen decimal() over money/smallmoney so you are over the first hurdle.

In general, it is recommended to choose decimal()(same as numeric() with the precision and scale you need. In this case, decimal(4,2) is fine as the maximum stored value will not exceed that precision and scale.

There is no storage size difference between decimal(4,2), decimal(5,2), or decimal(9,2).

decimal storage size is based on the precision: 

+-----------+---------------+
| Precision | Storage bytes |
+-----------+---------------+
| 1 - 9     |             5 |
| 10-19     |             9 |
| 20-28     |            13 |
| 29-38     |            17 |
+-----------+---------------+

The precision and scale can change when you perform certain operations in SQL Server. In your example, when you divide decimal1 by decimal2, your return type will be decimal, and the precision and scale will be determined by the following table:

+------------------------------+-------------------------------------+----------------+
|          Operation           |          Result precision           | Result scale * |
+------------------------------+-------------------------------------+----------------+
| e1 + e2                      | max(s1, s2) + max(p1-s1, p2-s2) + 1 | max(s1, s2)    |
| e1 - e2                      | max(s1, s2) + max(p1-s1, p2-s2) + 1 | max(s1, s2)    |
| e1 * e2                      | p1 + p2 + 1                         | s1 + s2        |
| e1 / e2                      | p1 - s1 + s2 + max(6, s1 + p2 + 1)  | max(6,s1+p2+1) |
| e1 union|except|intersect e2 | max(s1, s2) + max(p1-s1,p2-s2)      | max(s1, s2)    |
| e1 % e2                      | min(p1-s1, p2 -s2) + max( s1,s2 )   | max(s1, s2)    |
+------------------------------+-------------------------------------+----------------+

* 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.

So choosing decimal(4,2) as may have some impact depending on the operations the value will be involved in, but any performance difference between the two may be negligible.

Reference:

In C#, a decimal divided by a decimal returns a decimal; a decimal divided by an integer returns a decimal; a decimal divided by a float returns an error without an explicit conversion.

Community
  • 1
  • 1
SqlZim
  • 37,248
  • 6
  • 41
  • 59
0

This stores all of the UK currencies. Is this the correct way to do it.

You are not storing currencies - you are storing denominations (or the face value of official notes and coins) of an assumed currency. Whether this is "correct" depends on what you are modeling - and no one but you knows that. I'll agree with @SqlZim about the datatype.

I do have strong reservations about the usefulness of an identity column. Is there a purpose for it or do you slap an identity column into every table as a standard practice? At very least, you should enforce the natural key.

And according to Wikipedia, you might be missing some values and there is some overlap between notes and coins in terms of value.

SMor
  • 2,830
  • 4
  • 11
  • 14