|
Home > Archive > microsoft.public.sqlserver.server > November 2002 > What data type to use for Percents?
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
What data type to use for Percents?
|
|
|
| Hi All,
I'm trying to figure out what data type I should be using for Percents.
Currently I have a TinyInt and am storing the value from 0-100. On my
display (VB application) I add or remove the '%' sign when saving or
getting the data to and from the database. I would like to know if there is
a better more practical way of handling this.
Thanks,
John.
| |
| Anith Sen 2002-11-10, 3:23 pm |
| If fractional percentages are not used, you can simple use
SMALLINT, otherwise you can use FLOAT(approximate), DECIMAL
(accurate) depending on the level of accuracy desired.
--
- Anith
| |
| Allan Mitchell 2002-11-10, 3:23 pm |
| I would use DECIMAL(5,2) if you need accuracy to 2 decimal places and will
never need a whole number > 3 in length.
--
Allan Mitchell (Microsoft SQL Server MVP)
MCSE, MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community for SQL Server
professionals - http://www.sqlpass.org
"JWR" <jwrugo@rcn.com> wrote in message news:ul$oLpLiCHA.1804@tkmsftngp12...
> Hi All,
>
> I'm trying to figure out what data type I should be using for Percents.
> Currently I have a TinyInt and am storing the value from 0-100. On my
> display (VB application) I add or remove the '%' sign when saving or
> getting the data to and from the database. I would like to know if there
is
> a better more practical way of handling this.
>
> Thanks,
> John.
>
>
| |
| Robert Carnegie 2002-11-11, 12:23 pm |
| It may be desirable to use a decimal fraction between 0.00 and 1.00
to represent a percentage between 0% and 100%, because then you can
use it in server-side maths without converting to a fraction; you can
write "SELECT colnumber * colpercent" instead of
"SELECT colnumber * colpercent / 100". This will be particularly
beneficial if you have several percentages to apply to one original
number at once, e.g. number of units in stock x unit price x estimated
shrinkage x sales tax x markup from cost price - say
130 units * $13.00 * 0.95 * 1.07 * 1.05, if you expect 5% of
goods to be stolen (leaving 95% in stock), 7% sales tax added,
and you want to make a profit of 5% (1.05) after all that.
Although I'm probably handling sales tax wrongly, here.
Anyway, it also means that you can write
"UPDATE pctable SET colpercent = colnumber / colnumber2"
instead of "SET colpercent = colnumber / colnumber2 * 100".
However, you'll probably want to convert fractions such as 0.531
back to a percentage 53.1% for showing them to the user. Perhaps
you can quietly make that conversion on the client.
"Allan Mitchell" <allan@no-spam.SQLDTS.com> wrote in message news:<uJ9cC2PiCHA.1804@tkmsftngp12>...
> I would use DECIMAL(5,2) if you need accuracy to 2 decimal places and will
> never need a whole number > 3 in length.
>
> --
>
>
> Allan Mitchell (Microsoft SQL Server MVP)
> MCSE, MCDBA
> www.SQLDTS.com
> I support PASS - the definitive, global community for SQL Server
> professionals - http://www.sqlpass.org
>
>
>
> "JWR" <jwrugo@rcn.com> wrote in message news:ul$oLpLiCHA.1804@tkmsftngp12...
> > Hi All,
> >
> > I'm trying to figure out what data type I should be using for Percents.
> > Currently I have a TinyInt and am storing the value from 0-100. On my
> > display (VB application) I add or remove the '%' sign when saving or
> > getting the data to and from the database. I would like to know if there
> is
> > a better more practical way of handling this.
> >
> > Thanks,
> > John.
> >
> >
|
|
|
|
|