It is time for the March T-SQL Tuesday blog party. For some time now I have been planning to blog about data types, so you can imagine my excitement when I saw that this month Brent Ozar (t|b) is asking us to blog about our favourite data types. Shout out to Brent Ozar for hosting and choosing such an interesting and useful topic.
Some of you may already know that I have dedicated a large part of my life studying maths and I LOVE numbers;the fact that I have chosen to talk about numeric data types in SQL Server probably will not be a surprise to anyone.
There is no one-size-fits-all data type and I want to highlight the importance of choosing the right one – doing so can not only improve the performance, but also the data integrity. I will walk you through the various numeric data types in SQL Server, indicating when each should be used.
Imagine you are working with a column that has two possible values- this may prompt you to work with Boolean data. Boolean allows only two possible values- “True” or “False”. In SQL Server, we don’t have Boolean;instead we have the data type BIT that can be used to store Boolean values.
BIT is an integer data type that can take a value of 0, 1 or NULL. With regards to storage, if we have 8 or fewer bit columns in a table, the columns are stored as 1 byte. If we have between 9 and 16-bit columns, the columns are stored as 2 bytes, and so on. Additionally, string values TRUE and FALSE can be converted to 1 and 0 into BIT values.
A good example of when to use BIT is a column containing “Gender” information – we have Male(M), Female(F) and unknown(NULL). However, BIT may not be the best choice for column storing “Status” information:although initially, you may think that values for status can only be “on” or “off”, later on, you may decide to add other classifications such as “in progress”, “archived” etc. Therefore, a better option for the “Status” column would be TINYINT which we will cover next.
SQL Server integer data types represent whole numbers (both negative and positive). SQL Server supports four variations of integer data types that have different ranges and storage requirements – TINYINT, SMALLINT, INT and BIGINT.
Some practical uses of integer data types include: using it to count values, store a person’s age, or use it as an ID key to a table. It is a good practice to use the smallest integer data type that will reliably suit your data. For example, to store data about people’s age we can use TINYINT as it is not likely that someone will be older than 255 years.
DECIMAL AND NUMERIC
NUMERIC(p,s) and DECIMAL(p,s) are exact data types that are defined by precision(p) and scale(s). Precision is the maximum number of total digits allowed, and scale is the number of digits to the right of the decimal point. The default value of p is 18 and s is 0, and for both these values, the minimum is 1 and the maximum is 38. To illustrate that, let us say we have a column as DECIMAL(10,4) – precision of 10 and scale of 4. Our column can safely store the number 123456.1234
According to the Microsoft documentation, NUMERIC and DECIMAL are synonyms and, therefore, can be used interchangeably. So why are there two data types for the same purpose? Actually DECIMAL and NUMERIC are almost the same, but after some research, I found that the small difference between them lies in the the fact that NUMERIC determines the exact precision and scale, while DECIMAL specifies only the exact scale and the precision is equal to or greater than the specified value.
DECIMAL is one of the most used numeric data types because of its accuracy, but you should always ensure the precision of the DECIMAL variable specified is enough to accommodate the values assigned to it.
MONEY AND SMALLMONEY
SQL Server provides two dedicated data types for storing monetary values. You can think of MONEY and SMALLMONEY as DECIMAL – Money is effectively the same as DECIMAL(19,4) while SMALLMONEY is effectively the same as DECIMAL(10,4). If you are planning to use MONEY you should have in mind that performing division and multiplication can cause rounding errors that result in the unintentional loss of precision. The cause of the problem is that MONEY only saves information up to the 4th decimal place and if your multiplication or division results in an integer that goes to the 5th decimal place or more, MONEY will round it off, causing an accuracy error.
To prevent the accuracy error, the recommendation (almost without exception) is to use DECIMAL instead of MONEY. DECIMAL doesn’t cut numbers short with a rounding error, so you’re multiplying and dividing the whole number, and thereby maintaining the accuracy of your calculations. MONEY is a good option only if you are a non-enterprise user of SQL Server and choosing that data type will optimise storage.
It is also worth mentioning that MONEY is the only data type that can accept formatted numbers (1,234.1234) and can also accept currency symbol prefixes with a number ($1,234.1234).
FLOAT AND REAL
The FLOAT data type accepts approximate numerical values meaning that not all values in the data type range can be represented exactly. The float data type is declared as FLOAT[(n)] where n is the number of bits that are used to store the mantissa of the float number – or in other words, n dictates the precision. If no precision is specified during the declaration, the default precision is 53. Attempting to assign a value larger than the declared precision will cause an error to be raised. REAL is similar to FLOAT but it is an IEEE standard floating point value, equivalent to FLOAT(24)
The main difference between DECIMAL and FLOAT is that DECIMAL data types are good for storing exact numbers but they can have up to 38 digits in total, in comparison to FLOAT data types which are really good for long numeric values where the number stored is an extremely close approximation. Note that using FLOAT and REAL inadvertently can lead to storage issues. They are the right choice if you are using scientific data that requires the properties of floating point numbers, but in most cases, the better choice will be to use Decimal.
BACK TO THE QUESTION
I hope that this helped you to understand the numeric data types in SQL Server a bit better. The original topic for this month was to blog about our favourite data type, however, I cannot choose only one magical universal data type as I believe each of them can be magical when used in the right circumstances. My formula for choosing the right data type is: understand your data- how it is used and how it may be used, plus have a good understanding of the different data types so you can select the best option when developing your databases.