Home » Beginner »Intermediate »sql server »sqlserverpedia-syndication »SSC »Tips »TSQL » Currently Reading:

SQL Math: Whole Numbers, Real Numbers

It’s been a long time since grade school math, and for some the details have become kinda fuzzy. I’m no [insert name of famous math genius here], but I’ve been able to explain some basic things over the years.  A common SQL Math Misunderstanding came up again recently with a fellow SQL practitioner, so off we go.

The Basics: Two Kinds of Numbers

There are (at least) two kinds of numbers: real numbers, and whole numbers.


1.2 is a real number. So is 1, and -4.3, and 0, and 24 1/2, and and π

Plus and minus infinity, anything with or without a decimal or fraction is a real number. 

Any number that could be used to measure or quantify anything in real life is a real number (e.g., 4 grams of flour, 1 1/4 hours till bedtime, 2,089.32 miles to Chicago, -14,259,891,873,204.32 in the U.S.A.’s bank account, etc.)


1 is a whole number (also called integer). So is 0, and -4, and 43.

Plus and minus infinity, anything without a decimal or a fraction is a whole number. 

So, whole numbers are a subset of real numbers.

Any number that could be used to count anything in real life (without using a decimal or fraction) is a whole number (e.g., I have 16,000 blue M&Ms, two cats, and I have -40 dollars in my bank account.)

I’m going to use the terms whole number and integer interchangeably for the rest of this article.

SQL Math: Division

Math – division, specifically – is slightly different for whole numbers and real numbers. If you open up Windows calculator and enter 6/600, you’ll get 0.01.   The number 6 has exactly .01 (one hundredth) of 600 inside it. But if you open up SQL server  and type in SELECT 6/600, you get 0. Why?

In SQL Server, every column, variable, and parameter requires a data type.  When you pass a constant (like 6 or 600) to the SQL engine, it assigns a data type to that constant.  SQL gives 6 and 600 the type INT, the integer data type.  (For the full set of constant typing rules, see the SQL Server BOL article Constants.)

Let’s start with M&Ms.  Our next division problem, 50/4, is made up of two whole numbers.  Let’s assume we’re divvying up M&Ms, and phrase the problem 50/4 as “How many whole groups of 4 M&Ms can I make out of this bag of 50 M&Ms?”  The answer is 12 (groups of 4 M&Ms). The question doesn’t care that we have 2 M&Ms left over. Integer math only cares about the number of groups of N items (where N is the number on the bottom of X/N) you can make.

Six Piddly M&Ms. 600? CAN NOT HAZ!

So back to our question: Why is the ansewr 6/600 zero instead of .01?  Because 6 and 600 are integers, and the problem 6/600 is “How many groups of 600 can I make out of this bag of 6 M&Ms?”  You can’t get even one group of 600 M&Ms out of a bag with 6 piddly M&Ms in it, so the answer is zero.

If you want the answer .01, then the question should be SELECT 6.0/600.0. This types the problem as real numbers (DECIMAL data type – though you could also use REAL or FLOAT), and it phrases the question as “Exactly how many whole and partial groups of 600 are in 6?”  The answer, as we’ve seen, is .01, meaning “there is exactly one-one hundredth of a group of 600 within 6.”  Or more traditionally, “I can divide 600 into 6 exactly .01 times.”

SQL Math: Modulus

A quick note on modulus – the remainder operator.  Remember remainders? When dividing in grade school, we said 50/4 is 12 remainder 2, meaning after all the whole number division was done, there were still 2 (M&Ms) left over. In SQL we’d find out the remainder using SELECT 50%4, which results in 2. See?

SELECT 50/4 AS [Answer to 50/4], 50%4 AS [Answer to 50%4]


Answer to 50/4  Answer to 50%4
12  2

That’s easy enough. But what happens if we want the modulus for 6/600?

SELECT 6/600 AS [Answer to 6/600], 6%600 AS [Answer to 6%600]


Answer to 6/600  Answer to 6%600
0  6

We know wny 6/600 is zero. But why is 6 modulus 600 = 6?  Because that’s what’s left over for this problem…600 went into 6 zero times, and we have a remainder of 6!

It sounds simple, but then, everything’s simple once you know the answer.

Finally: In mathematics, dividing real numbers results in no remainder, because we’re dealing with exact numbers. 6.0/600.0 is equal to .01, and there is nothing left over. BUT SQL Server does allow you to get the modulus (remainder) for real numbers: For example, SELECT 6.0%600.0 = 6.0, and 6.4%600.0 = 6.4.

Further reading in BOL:

Using decimal, float, and real Data

Using integer Data

Data Type Precedence (Transact-SQL)


Let me know if you have any other stumbling blocks when it comes to SQL and math. If I don’t know the answer, I can find out who does!

Happy days,
Jen McCown

Currently there are "2 comments" on this Article:

  1. Emmad says:

    Very interesting content and header!

Comment on this Article:

Release Date: Minion by MidnightDBA

Just 1 day 22 hours 38 minutes 28 seconds until we release Minion Redindex: index maintenance done right!

MidnightSQL Consulting

Need help? Got an emergency? Write us at Support@MidnightDBA.com!

We can schedule time to help with your backup/restore issues, high availability and disaster recovery setup, performance problems, and a great deal more. Very often, we're even available on the moment for downtime issues and emergencies.

For more information about MidnightSQL consulting, email us or check out www.MidnightSQL.com. Happy days!

Where are We?

November 3-7: PASS Summit, Seattle, WA

PASS Summit: Jen is presenting How to Interview a DBA: A Panel Debate on Thursday 11/6 1:30pm, room 401 (along with Adam Machanic, Sean McCown, Bob Pusateri, and Michelle Ufford).

PASS Summit: Sean is presenting Performance Tuning Your Backups on Wednesday 11/5 3:00pm, room 602-604.

December 11: Presenting "Powershell Cmdlets.." at Alaska SQL Server User Group

January 30: "Become an Enterprise DBA" precon at Austin SQL Saturday

Blog Posts by Category


How to Eat Pop-tarts
Watch DBAs@Midnight live on Fridays,m 11pm Central time

The best database career advice you’ve never heard!


The DBA Roadmap Seminar is 7 MP3 tracks (over 5 hours!) of insider guidance on your database career. We'll teach you how and what to study as a DBA, weigh in on controversial resume debates, teach you to recognize a worthy recruiter, and discuss the new professionalism of interviews. Also some bonus materials, PDF companion guides, and really spiffy intro music!

Once your $99 PayPal payment is submitted, you'll get the download link in e-mail! (Download is a 370Mb ZIP file.)

Become a DBA. Become a BETTER DBA. Use the Roadmap.

Visit www.DBARoadmap.com for info, forums, and more!

Add to Cart View Cart

Cunningham’s Law

"The best way to get the right answer on the Internet is not to ask a question, it's to post the wrong answer."
Relevant: http://xkcd.com/386/