T-SQL Tuesday #136: Blog About Your Favourite Data Type

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.

BIT

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.

INTEGER

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.

1 thought on “T-SQL Tuesday #136: Blog About Your Favourite Data Type

  1. Pingback: A Review of Numerical Data Types – Curated SQL

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s