SQL Server - best datatype for a percent field

Asked By archuleta3 on 22-Feb-07 08:34 AM
In terms of best practices, what is the best datatype to use for a field that
holds a percent, as with a sales commision percentage? I have three field
that I'm working on and would like to set them up according to best practices:

payment - > smallmoney
commission_rate -> decimal(???), numeric(???)
commission - > smallmoney (calculated: payment * commission_rate)

If the jury is out on the best practice for how this datatype should be set,
then great I'd love to hear the answer. If there is room for some debate,
depending on circumstance or something like that, then that would make for
some interesting reading. Any feedback is much appreciated.

Marc




Anith Sen replied on 22-Feb-07 09:42 AM
The fundamental of type selection is operations -- i.e the basic type
selection criteria for any attribute depends upon the operations that would
be performed on the values of this attribute.

Generally, competent developers can intuitively assign types without have to
go through rigorous research process. For instance, if the percentage is
represented as fractional values, once could opt for decimal. If it requires
that the percentage values be integral then one could opt for integer or
small integer.

--
Anith
Roy Harvey replied on 22-Feb-07 09:44 AM
First, it is generally not a good idea to use money or smallmoney.
They are holdovers from the days before SQL Server supported decimal
and numeric, and have several strange behaviors we could all do
without.

Percentages are best stored as decimal.  Decide the exact precision
you need and specify it.

If all three columns are in one table it is redundant to carry the
commission column at all.  Consider creating it as a computed column.

Roy Harvey
Beacon Falls, CT

On Thu, 22 Feb 2007 05:34:38 -0800, archuleta37