Knowing the difference between all the integer data types available to us in Microsoft SQL Server is very important when it comes to the task of designing and developing SQL Server databases.
Most people don’t realize that a data type is essentially a constraint, meaning the data type you choose limits the kinds of values you can store. For example, you can’t store the value ‘Circus’ in a column with the INT data type. The word ‘Circus’ obviously isn’t an integer.
But also, even if you are storing the correct kind of data, you need to think about which data type would be the most appropriate for the data you want to store.
For example, you shouldn’t store something like exam scores in a data type like BIGINT because that is much larger than you need. Exam scores can range from 0 to maybe 110 with extra credit? We’ll learn how BIGINT would be extreme overkill for storing such a small number.
Do you know all the integer data types, and the differences between them? Do you need to know when you should choose one integer data type over another?
In this tutorial, we will answer these questions and more!
There are several integer data types referenced in this FREE guide:
FREE EBook on SQL Server Data Types!
Definitely download that guide if you want to learn about other very common data types you’ll likely use as a database professional.
We will be discussing these topics in this tutorial:
- Why it’s important to choose the best data type for your data
- INT data type
- BIGINT data type
- SMALLINT data type
- TINYINT data type
Let’s get to it:
1. Why it’s important to choose the best data type for your data
The reason it’s important to choose the best data type for your data is because different data types require different storage sizes to store the data.
For example, the INT data types requires 4 bytes of memory to store an INT value. Compare that to BIGINT which needs 8 bytes to store a value.
If you chose to use BIGINT as your data type when you could have used INT, you are wasting space. In that example, your numbers would use twice as much space in memory for no good reason. NOT GOOD.
You need to use the smallest data types to suit your needs. If you know the number you need to store will never go above, say, 30,000, you might get away with using the SMALLINT data type. But also remember, if there is a chance you might store a number larger than 30,000 (say 50,000), you probably don’t want to use SMALLINT.
Whenever that chance occurs and you need to store 50,000, if your data type is SMALLINT, you won’t be able to. You will get an error message if you try. So at that point, you’re out of luck.
It’s very important to strongly think about your data types before you establish them, especially when it comes to table columns. You want to use the smallest data type needed to store your values, and make sure it can hold everything you could possibly need it to.
2. INT data type
Of all the integer data types available to us, the INT type is the most common one I see as a software and database developer. The INT data type is definitely one you should be familiar with.
INT is a middle-of-the-road integer data type. It isn’t the largest we have available, but it isn’t the smallest either. That’s why it is applicable to many situations.
I see many primary key columns use an INT data type. Since each value in a primary key column needs to be unique, that means we can potentially store billions of unique rows in a table. That’s so much room to grow!
Do you think it’s too much room to grow? Do you not foresee your tables needing to store a primary key integer value larger than about 2 billion?
Well, let’s think about the alternative. The next-smallest integer data type after INT is SMALLINT. When we discuss SMALLINT, you’ll see how it’s range is much smaller than INT.
Again folks, you need to use the best data type to suit your needs.
Here are the details for INT:
Range: -2,147,483,648 to 2,147,483,647
Storage: 4 Bytes
3. BIGINT data type
Need to store a big-a** number? You should think about using BIGINT.
I truly don’t see this data type used much. I’m not saying it doesn’t have it’s time and place, but I personally don’t see it very much at all.
This data type will store a positive/negative number as big as 9 quintillion.
“Quintillion?”
So it goes million, billion, trillion, quadrillion, then QUINTILLION.
As far as an example of when you would use this data type, use your imagination. Maybe you need to store the number of inches between here and mars? (spoiler alert: It’s still not even close. Earth is about 13 trillion inches away from mars).
Remember, this data type might be overkill. If you know, without a doubt, that your data won’t even come close to 9 quintillion, you should choose to use a smaller data type like maybe INT. This is especially important given how much difference in storage is required for BIGINT. It requires 8 bytes of storage, versus the 4 bytes of storage for INT.
If my math is correct, that’s twice as much. It would be so wasteful to use BIGINT if you don’t need to.
Remember folks, think about your data types before using them, especially when it comes to data types of columns. If you say “I’ll just use BIGINT, that way I don’t need to worry about anything“, you would be making a huge mistake!
Here are the details for BIGINT:
Range: -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807
Storage: 8 Bytes
4. SMALLINT data type
Ok, now let’s go in the opposite directions and think about smaller integer data types.
The SMALLINT data type can hold a positive/negative number a bit bigger than 32,000.
Maybe you need to store the number of Employees in your company? Or maybe the number of Products in your inventory? SMALLINT might be all you need.
And again, the advantage is SMALLINT uses less space per number than INT would.
Here are the exact numbers for SMALLINT:
Range: -32,768 to 32,767
Storage: 2 Bytes
5. TINYINT data type
And alas, we come to the smallest integer data type. This one is super small. You can only store a number as large as 255 in this data type.
The interesting thing about this data type is how it doesn’t store negative numbers. To be clear, the range of number you can store using TINYINT is 0 to 255.
Again folks, this is a very restrictive integer data type.
The exam scores example I mentioned at the beginning of this tutorial is a good one. Exam scores usually can’t be negative (I would think), and the score usually doesn’t go much higher than 100.
Another example might be a person’s age in years A person’s age is never a negative number, and most people don’t live much past 100 at the most (for now, anyway).
This data type also uses the smallest amount of storage, weighing in at a whopping 1 byte!
TINYINT is a fun data type to think about, but it’s another one I don’t ever see in the real world. But I’m sure it has it’s time and place!
Here are the details for TINYINT:
Range: 0 to 255
Storage: 1 Byte
Next Steps:
Leave a comment if you found this tutorial helpful!
If you need a crash course on the most common data types you should know, make sure you download this FREE guide:
FREE EBook on SQL Server Data Types!
In this FREE Ebook, you’ll learn about many other common data types, including CHAR, VARCHAR, DATETIME, and DECIMAL. These are very common data types you will see in the real world, so you should definitely make yourself familiar with them!
Also, I have another deep-dive tutorial on the character string data types. Check it out here:
SQL Server character data types (and the differences between them)
Thank you very much for reading!
Make sure you subscribe to my newsletter to receive special offers and notifications anytime a new tutorial is released!
If you have any questions, please leave a comment. Or better yet, send me an email!
