T-SQL 101: 39 Numeric operators in SQL Server

Today, I’m continuing the discussion on the variety of data types supported by SQL Server. I’ll round out the discussion by looking at the operators that are used with the numeric data types. Here are the operators:

Some of these are fairly obvious but even then, they can have hidden complexity.

We use the plus sign + to add numbers, and we use the minus sign (or dash) - to subtract numbers. No surprise there.

Multiplication uses the asterisk * and is also easy enough. However, keep in mind that when you use multiplication, you probably should also be thinking about rounding if you are working with decimal values, and not just integers.

When you multiply an integer by a decimal, the result is promoted to being a decimal (i.e. the higher data type).

Division uses the slash / sign. What confuses most people is how it applies to data types. For example, what would you expect to see output from this query:

SELECT 10 / 4;

If you said 2.5, you wouldn’t be alone, but you’d also be wrong. It would output the value 2.

When you divide an integer by an integer, the result is an integer, not a decimal.  Now if you executed this instead:

SELECT 10.0 / 4.0;

you would see 2.5 as the answer. A decimal divided by a decimal returns a decimal.

But what would happen to this one:

SELECT 10 / 4.0;

Again, you’d get 2.5 as the answer. An integer divided by a decimal will return the higher data type i.e. a decimal.

Finally, the % sign is the modulo operator. You can think of modulo as the remainder after a division. So this query:

SELECT 10 % 3;

would return the value 1. That’s because as integers, 10 divided by 3 is 3, with a remainder of 1.

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-10-14