The SQL Server DECIMAL data type is a very common data type you will see and use in your career as a database professional. It’s important for you to know how it works and the rules for setting it up.
DECIMAL is, of course, ideal for when you need to store a number with decimal digits. SQL Server gives you the ability to specify how many decimal digits should be stored for your number. This gives you control of how much space in memory your number takes up.
Do you need to know how the SQL Server DECIMAL data type works? You’ve come to the right place!
The DECIMAL data type is featured in the following FREE guide:
FREE EBook on SQL Server Data Types!
You should definitely download this guide if you need to learn about other very common data types you will encounter as a database professional. This guide will be a great resource for you to reference during your career. Download it today!
We will discuss the following topics about the SQL Server DECIMAL data type:
- An example of using a SQL Server DECIMAL data type
- The “Precision” of a DECIMAL value
- The “Scale” of a DECIMAL value
- Storage requirements for DECIMAL values
- Tips and tricks
Let’s start from topic 1.00 🙂
1. An example of using a SQL Server DECIMAL data type
Let’s create a simple table called Books, which will have a DECIMAL column to store the Price of a book:
CREATE TABLE Books ( BookID INT PRIMARY KEY IDENTITY, Title VARCHAR(50), Author VARCHAR(20), Price DECIMAL(5,2) )
The syntax for DECIMAL is this: DECIMAL(p, s)
The “p” stands for “Precision”. This is the total number of digits we can store, both from the left and the right sides of the decimal point.
The “s” stands for “Scale”. This is the number of digits we can store to the right of the decimal point.
If you think about it, the number of digits we can store to the left of the decimal will be p – s. So in our example, we can store up to 3 digits to the left of the decimal when storing the Price of a book.
So let’s think about the kinds of numbers we can store in this SQL Server DECIMAL column.
Examples of numbers we can store
Will this INSERT statement work?: INSERT INTO Books VALUES ('As a man thinketh', 'Allen', 14.95)
Yep. There are only 2 digits to the left of the decimal. Remember earlier, we said we could have a maximum of 3. And we have 2 digits to the right of the decimal, satisfying the scale requirement.
How about this one?: INSERT INTO Books VALUES ('Eat that frog!', 'Tracy', 12.449999)
The answer is also yes, we can.
Here’s the thing about numbers to the right of the decimal place: If there are more digits specified than are allowed according to “s“, those extra digits will be removed and the number will be rounded to the nearest “s” decimal place.
After running that INSERT statement, here is what the row looks like:
So the number 12.449999 obviously has more than 2 digits after the decimal, which is all our data type is allowed to store. Therefore, when SQL stores that number, it will round off the decimal to the 2nd digit and cut off everything after that.
In SQL talk, this is what’s known as an implicit conversion. We’re actually converting a number with a higher precision and scale to a number with a lower precision and scale. SQL Server uses rounding in that scenario.
The number 12.449999 appears to be a data type of DECIMAL(8,6). The data type of our column is only DECIMAL(5,2), which has, of course, a lower precision and scale. Therefore, SQL Server uses rounding for the decimal places.
Finally, can we store this value?: INSERT INTO Books VALUES ('T-SQL Fundamentals', 'Ghan', 109.449999)
Yep, it’s all good! We have the maximum number of digits allowed to the left of the decimal (3), and the digits to the right will get rounded.
Bonus Example:
What about this insert?: INSERT INTO Books VALUES ('The Book of Law', 'Carter', 2500.89)
Nope. We get an error message:
When it comes to digits to the left of the decimal place, the whole idea of “rounding” goes out the window. There is a strict limit to the number of digits allowed to the left of the decimal, and SQL Server will not round anything if you exceed that limit.
In our case, the number of digits allowed on the left side of the decimal point is 3, remember? So I am simply not allowed to store a number with 4 digits (or more) to the left of the decimal.
Finally, what about this example?: INSERT INTO Books VALUES ('American Fire', 'Hesse', 24)
We didn’t specify any decimal places, so will it work?
Yep. SQL Server simply converts that to 24.00, satisfying the number of digits to the right of the decimal (a.k.a. the “scale”):
2. The “Precision” of a DECIMAL value
Let’s talk a bit more about the “p” value. As we know, “p” represents the total number of digits that can be stored, both to the left and right of the decimal place.
The highest number that “p” can be is 38. To reiterate, this means the total number of digits you can store using the DECIMAL data type is 38 digits.
The minimum value that “p” can be is 1.
3. The “Scale” of a DECIMAL value
As we know, “s” represents the number of digits we can store to the right of the decimal point.
The “s” value can be anything from 0 through “p”.
Let’s think about that a minute. Could you have a DECIMAL type of DECIMAL(5, 7)?
Well, that’s saying we can have 5 digits total, with 7 of them being to the right of the decimal place…
Folks, that doesn’t make any sense.
We could definitely have DECIMAL(5,5) though, right? That’s saying we have a total of 5 digits, and all of them are to the right of the decimal place.
That’s fine! We’ll just never be able to store a number greater than .99999. Maybe that’s what we want!
4. Storage requirements for DECIMAL values
The amount of storage your DECIMAL values will use depends on the “p” value. Here’s the breakdown:
Precision Storage Requirement 1-9 5 Bytes 10-19 9 Bytes 20-28 13 Bytes 29-38 17 Bytes
This is one of the nice things about the DECIMAL data type. You can basically use the smallest precision you think you will need to store your data. The smaller the precision, to smaller the space needed to store the data.
For example, if you know none of your books will be priced over $999, and you don’t care to see more than 2 decimal places, you should go with the DECIMAL(5,2) data type. That’s no more than 3 digits to the left of the decimal, and no more than 2 to the right. The values in that column will all use only 5 Bytes of storage.
If someone didn’t understand the DECIMAL data type, they might just set it up as DECIMAL(30,10) because “heck, it’s always better to have more space than less space, right?“.
NO. Your values will use more than 3X the amount of storage you really need. Good going.
Do you see why it’s important to know a thing or two about data types?
5. Tips and tricks
Here is a list of tips and tricks you should know about the SQL Server DECIMAL data type:
Tip # 1: The default precision and scale is (18,0)
It’s possible to leave off the “p” and “s” values completely and just say something like:
DECLARE @bookPrice DECIMAL
The default precision and scale of our new @bookPrice variable will be (18,0).
That’s 18 digits total, with none of them being to the right of the decimal.
I guess in other words, we can only store a whole number that is 18 digits big.
If you wanted to do that, you could’ve just used a regular integer data type like INT or BIGINT.
I think it’s much more common to specify exactly the precision and scale you want, rather than just going with the default.
Tip # 2: You can specify just a “p” value
This works just fine:
DECLARE @bookPrice DECIMAL(5)
Again, in this @bookPrice variable, we specified we want to store a number with a total of 5 digits, with none of them being to the right of the decimal place.
Basically, a whole number that is 5 digits big.
Tip # 3: An “s” value can only be specified if “p” is also specified
From our last point, you understand that if a single number is specified in parentheses, SQL Server will interpret that as the “p” value you want to use. There is no way to specify only an “s” value and no “p” value.
The only way you can specify an “s” value is if a “p” value is also specified.
Tip # 4: DECIMAL and NUMERIC are the same thing
Not much to say about this tip, really. They are synonyms in SQL Server and can be used interchangeably.
Check out the full tutorial on DECIMAL vs NUMERIC.
Next Steps:
Leave a comment if you found this tutorial helpful!
To be a data type MASTER, you should download the following FREE guide:
FREE EBook on SQL Server Data Types!
The guide is a discussion about the most common data types you will see during your career as a database professional. It will definitely be a great resource for you to reference throughout your career. You won’t regret knowing these data types, trust me. Get it today!
I also have a specific tutorial on the various integer data types we have available to us in SQL Server. Definitely check that out:
SQL Integer Data Types – Everything you need to know
Do you know the difference between CHAR and VARCHAR? How about CHAR and NCHAR?
Thank you for reading!
Make sure you subscribe to my newsletter to receive special offers and notifications anytime a new tutorial is released!
Thank you for reading! If you have any questions, or if you are struggling with a different topic related to SQL Server, I’d be happy to discuss it. Leave a comment or visit my contact page and send me an email!
