Analysing the SQL Server Numeric Data Types

A common challenge for database modellers is deciding which data type is the best fit for a particular column. It is a problem which involves consideration of both the properties and the scale of the data that will be stored, and in no case is this more evident than when handling numeric values because of the large variety of alternatives that most relational databases provide for their storage.

In this article we’ll explore the numeric data types available in SQL Server and analyse their advantages, disadvantages and general usage.

Storing Whole Numbers

Integer types are the most commonly used types of numerical data. These data types are used to write whole numbers, that is, numbers used for counting and ordering in contrast to decimal numbers.

Even then, though, there are several varieties of integer data types of varying range and storage size. Integer types of larger range will be heavier in terms of storage size.

Since integer types are used for counting, they are the best data type to use for a table’s primary key. Even then, though, smallint and tinyint aren’t very widely used for this purpose. Since int is such a lightweight data type in the first place, it isn’t considered bad practice to use it for primary keys in general. When very complex queries are executed, though, or if it’s necessary to cut some corners for extra space, one might want to consider using the lightest type possible. In this case it’s important to think ahead and consider the potential growth of the database.

Storing Decimal Values

The most straightforward and reliable way for storing non-whole numbers is by using the decimal and numeric data types. These alternatives allow you to write numbers to both the left and right of the decimal point, which permits the storage of rational numbers instead of just natural ones.

The second main difference is that, instead of the fixed size and range of the integer types, decimal and numeric allow the user to specify the desired length of the entries. This is set up with a function of two variables, with the first value setting up the precision and the second one setting up the scale. Precision refers to how many digits can be written, while scale refers to the number of decimal places, or numbers at the right of the decimal point. A scalable number of precision 6 and scale 4 can range from -99.9999 to 99.9999, and is written as decimal(6,4) or numeric(6,4), as it were.

The decimal and numeric data types are functionally identical, varying only slightly in the way SQL creates the entries, but with an irrelevant difference in the weight or processing time. With this said, though, technically speaking differences in precision and scale make different data types, meaning that decimal(6,4) and decimal(3,1) are different data types with different sizes, so you must calculate what will be the required precision and scale for your data. Once again, it’s important to think ahead.

Decimal and numeric types allow for a precision of up to 33, making them incredibly versatile, and even useful for integer values if bigint results are ever insufficient.

Subjectively, I believe that decimal is the better option of scalable data type, since it better communicates its function, which is to store numbers with a decimal point, while numeric might be too generic a name which doesn’t specify what kind of numbers it handles.

Storing Fractions

At first sight, float and real would seem like more efficient, lighter and faster versions of decimal and numeric, allowing a precision of up to 15 digits with a far lighter weight and up to 20 times faster when handling very large numbers. With this said, there’s a caveat to using them.

Float and real values are stored as binary, and not decimal, data. What this means in practice is that they don’t always store the exact value that was input. This is most notable when dealing with numbers that have lots of decimal places or when performing mathematical operations. In reality, the best circumstance for using this data type is when handling numbers where the exact value is either unknown or irrelevant. They are good for scientific data, which isn’t usually decimally accurate to begin with, so making calculations with it allows for a certain margin of error. This same luxury doesn’t apply when handling money, for example, where every single cent, and in some cases fractions of cents, counts.

The two varieties of approximate types are float(n) and real. The value of n specifies how many bits will be used for storage. It can be any number between 1 and 53, its default value. The real data type is the same as float(24), and therefore has a storage size of 4 bytes. The storage size of a float entry varies depending on the value of n, but will be 8 bytes if none is specified.

Storing Monetary Values

As the name implies, these data types are specifically designed for handling money, with relatively small sizes, storage of currency symbols as well as the number, and an appropriate level of precision.

The alternatives that SQL Server offers are money and smallmoney. They differ in how large of a number they can store, and both have a scale of 4 digits. They can work with 34 different currency symbols. This doesn’t affect the monetary value, though, so if you were to change the symbol used for an entry the value will still remain the same.

It’s of note that neither Oracle nor MySQL have their own data types specifically for handling currency.

Data Conversion

I think it’s necessary, as a closing statement, to mention the fact that yes, if the necessities of a database were to change or if an error was found, it’s possible and at times very easy to change the data type. Here’s a couple of things to have in mind when doing so.

• Float or real values that are converted to integer types will be truncated automatically.
• Converting from a scalable data type to an integer type will round up the value. Converting to float or real can cause loss of precision. Converting any other data type to decimal or numeric can cause overflow.
• When you convert an integer value to a currency data type the value will be expressed in the selected monetary unit.

It’s better not to count on conversions while designing the database, but if it comes to it you must always be careful not to lose any data.