T-SQL 101: 36 Exact whole numbers in SQL Server

I previously mentioned that SQL Server supports a wide variety of data types. The first of these are what we would consider exact numbers. Here are the available types:

So there’s a tinyint that’s basically one byte in size. One byte (8 bits) gives us 2^8 possible values, so we have 256 possible values. The first one is zero, so we have values up to 255.

Note: tinyint doesn’t allow for negative values.

A smallint is 2 bytes, and lets us have 2^16 possible values. Because we allow for both positive and negative values, we have a range from -32768 to 32767.

An int is 4 bytes, so we have values from -2147483648 to 2147483647. That’s already a pretty big range of values, and for that reason, int tends to get used widely. For example, most companies don’t have more than 2 billion customers (and that’s just the positive range).

A bigint is 8 bytes and allows for crazy-big numbers. For most practical databases, you aren’t going to run out of bigint values.

A bit is only a single byte. It can be 0 or 1, and like the other integer data types, it can also have no value i.e. be NULL.

How big is bigint?

A while back, I saw a customer moving their identifiers to the uniqueidentifier data type (we’ll talk about it later), because they were worried about running out of bigint values!

It’s hard to comprehend how large bigint is. In fact, I doubt that you really can.

Back in the early 1990’s, I saw an article that talked about processing numbers. They said that if you took and 8 bit computer, put a zero in an 8 bit register, and put it in a loop incrementing that value, that you’d finish (get to the top value) in the blink of an eye. After all, it’s only 256 operations before you overflow.

For a 16 bit value, and 65536 increments, again it would still happen in the blink of an eye.

For a 32 bit value, and it’s 4 billion odd increment operations, it’d still be pretty quick.

But for a 64 bit value, on the fastest computer available on that day, it was over 350 years. Yep, all day, every day, for 350 years, just adding one, to get to the top value.

Now computers are faster now than they were then, but 64 bit values are really crazy large.

Note: when we get to uniqueidentifier later and its 128 bits, you’re talking about values that are outside all our comprehension of time and space.

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-23