What datatype should I use for numeric columns?
Some people just accept whatever datatype was assigned by the Access upsizing wizard, or a DTS import; others just use MONEY if they're storing prices or salaries, or INT if they know they don't need decimal places.
There should be more careful thought taken when considering datatypes for efficient and scalable database design. First, let's review the different datatypes, numeric capacities, and storage requirements.
* Up to 8 BIT columns can share 1 byte.
Now, let's examine some known limitations with a few of these datatypes.
The BIT datatype doesn't have enough selectivity to make an index useful (though people seem to always want to apply an index to a BIT column). Keep in mind that BIT is not equivalent to BOOLEAN; BIT allows three values (0, 1, or unknown), while a true BOOLEAN type requires exactly two values (TRUE or FALSE). There is no BOOLEAN datatype in SQL Server, and this can cause some confusion.
SMALLMONEY / MONEY
An important problem with these datatypes is the potential loss of precision when using multiplication or division. For example, compare these results:
The results are hardly shocking, and in small calculations like the tax on a pair of shoes, are not likely to be an issue. But for larger transactions, this behavior could certainly mean a tangible difference in the outcome.
REAL / FLOAT
The problem with the REAL and FLOAT datatypes is that the store a close, but not exact, representation of the value. And while they follow the IEEE 754 specification for approximate data types, this can still cause some highly unexpected results (see Article #2477 for an example).
So, what now?
Now that we've touched on the capabilities and a few of the problems with each of the numeric data types, how do you know which to use in your situation?
For whole numbers, use the smallest datatype that will hold your maximum potential value. This will make indexes on the column perform their best, and will keep your table size down. It is easy to later a column to make it wider, if you later realize you need to allow larger values; it can be much more difficult making a column more narrow when you need to recover space.
INT is probably a better option for salary than MONEY, since decimal points are usually not needed. SMALLINT would be suitable if you work at a non-profit with very low salary caps. :-)
REAL and FLOAT should be avoided when possible. Since approximate numbers can lead to imprecise results, opt for NUMERIC or DECIMAL. Each are functionally equivalent; I tend to use DECIMAL, if only because it better describes the nature of the data.
For a column that stores an hourly wage, a DECIMAL with a precision of 5 and scale of 2 (see footnotes for definitions) is probably a better choice than MONEY or SMALLMONEY. Both of the latter types have far too much precision and scale to be useful for a value that is almost always going to be less than $40.00.
I don't know of any true advantages to using the MONEY / SMALLMONEY datatypes; while there are specific STR and CONVERT expressions that can format money in desirable ways, I usually leave this formatting to the client (see Article #2188). Because of the problems with multiplication and division of money values (e.g. calculating a discount), it is usually safer to use a DECIMAL data type. This also allows you to keep the datatype as small as it needs to be. For example, an online store that sells bee and honey equipment does not need to support the upper bound of the SMALLINT datatype (over $214,000!), never mind the upper bound of the MONEY datatype.
What are precision and scale?
NUMERIC and DECIMAL datatypes are defined as:
Precision is the number of decimal places that the column can take; scale is the number of decimal places allowed to the right of the decimal point. So, if you need to store percentages (e.g. 48.97%) you would use DECIMAL(5,2) — assuming you needed to cover the 100.00% case. If you wanted more accuracy, you could use DECIMAL(8,5), which would allow a value like 48.96523%.
There are known problems with the ISNUMERIC() function in T-SQL. Keep in mind that, just because a value returns ISNUMERIC(value)=1, that does not mean that the value can be converted seamlessly to any of the above data types. See Article #2390 for more information.
Related ArticlesHow do I build a query with optional parameters?
How do I calculate the median in a table?
How do I create a store locator feature?
How do I deal with MEMO, TEXT, HYPERLINK, and CURRENCY columns?
How do I deal with multiple resultsets from a stored procedure?
How do I debug my SQL statements?
How do I determine if a column exists in a given table?
How do I enable or disable connection pooling?
How do I enumerate through the DSNs on a machine?
How do I find a stored procedure containing <text>?
How do I get a list of Access tables and their row counts?
How do I get the latest version of the JET OLEDB drivers?
How do I handle alphabetic paging?
How do I handle BIT / BOOLEAN columns?
How do I handle error checking in a stored procedure?
How do I ignore common words in a search?
How do I page through a recordset?
How do I present one-to-many relationships in my ASP page?
How do I prevent duplicates in a table?
How do I prevent my ASP pages from waiting for backend activity?
How do I prevent NULLs in my database from mucking up my HTML?
How do I protect my Access database (MDB file)?
How do I protect my stored procedure code?
How do I protect myself against the W32.Slammer worm?
How do I remove duplicates from a table?
How do I rename a column?
How do I retrieve a random record?
How do I return row numbers with my query?
How do I send a database query to a text file?
How do I simulate an array inside a stored procedure?
How do I solve 'Could not find installable ISAM' errors?
How do I solve 'Operation must use an updateable query' errors?
How do I temporarily disable a trigger?
How do I use a SELECT list alias in the WHERE or GROUP BY clause?
How do I use a variable in an ORDER BY clause?
Should I index my database table(s), and if so, how?
Should I store images in the database or the filesystem?
Should I use a #temp table or a @table variable?
Should I use a view, a stored procedure, or a user-defined function?
Should I use recordset iteration, or GetRows(), or GetString()?
What are all these dt_ stored procedures, and can I remove them?
What are the limitations of MS Access?
What are the limitations of MSDE?
What are the valid styles for converting datetime to string?
What datatype should I use for my character-based database columns?
What does "ambiguous column name" mean?
What is this 'Multiple-step OLE DB' error?
What is wrong with 'SELECT *'?
What naming convention should I use in my database?
What should I choose for my primary key?
What should my connection string look like?
When should I use CreateObject to create my recordset objects?
Where can I get this 'Books Online' documentation?
Where do I get MSDE?
Which database platform should I use for my ASP application?
Which tool should I use: Enterprise Manager or Query Analyzer?
Why are there gaps in my IDENTITY / AUTOINCREMENT column?
Why can I not 'open a database created with a previous version...'?
Why can't I access a database or text file on another server?
Why can't I use the TOP keyword?
Why do I get 'Argument data type text is invalid for argument [...]'?
Why do I get 'Not enough space on temporary disk' errors?
Why does ASP give me ActiveX errors when connecting to a database?
Should I use COALESCE() or ISNULL()?
Where can I get basic info about using stored procedures?