T-SQL 101: 37 Exact decimal numbers in SQL Server

Today, I’m continuing the discussion on the variety of data types supported by SQL Server. Last time I mentioned exact whole numbers, but there are also exact decimal numbers. Here are the available types:

When I need to work with numbers with decimal places in them, the data type that I normally use is decimal.

It has a fixed precision and scale so for example, if I say:

decimal(18,3)

what I mean is up to 18 digits, with up to 3 decimal places. There’s another data type called numeric, which for the purposes of this course you could think of as basically being just equivalent to decimal.

Shouldn’t money be used to store money?

Simple answer: No

The data types money and smallmoney are old Sybase datatypes, still hanging around in SQL Server and T-SQL. They aren’t ANSI standard types (although that’s not the biggest issue with them). They are restricted to a fixed scale of 4 decimal places.

When you are using any decimal values, you should be choosing the precision and scale yourself, and working with rounding values as needed, to get the right outcome.

In general, we would suggest you avoid small money and avoid money as data types work with decimal instead.

Note: There are some gigantic data warehouses where people like money because it used smaller storage but now with row compression or better, that’s just not an issue any more.

So why are money and smallmoney still in SQL Server?

The SQL Server team places a high value on backwards compatibility. Removing the data types would break way too much existing code.

I feel sorry for people new to SQL Server who decide they need to store amounts of money though, and they see a data type called money, so they presume they should use it. Maintaining backwards compatibility has its downsides as well as its upsides.

Learning T-SQL

It’s worth your while becoming proficient in SQL. If you’d like to learn a lot about T-SQL in a hurry, our Writing T-SQL Queries for SQL Server course is online, on-demand, and low cost.

2019-09-30