TinyInt vs SmallInt vs int vs BigInt, does size matter?

Size matters – I don’t care what your girlfriend says.

When creating tables, deciding on what data types to use is important in regards to long term performance from your SQL Server. In this post we will be comparing tinyint, smallint, int and bigint.

We will be looking at how storage size and memory usage differs between the data types, and how this impacts performance.

I created four tables for each data type, each with five columns and five million records. Below is an example of one of those tables, using tinyint.

CREATE TABLE [Tinyint] (
Col1 tinyint,
Col2 tinyint,
Col3 tinyint,
Col4 tinyint,
Col5 tinyint
)

This isn’t a ‘real world’ example, but the purpose of this post is to showcase the impact on performance between the data types – at scale, the difference will become more apparent.

First, let’s examine storage size and memory usage.

Storage Size

Below, I have executed a query to display the differences in storage size for each data type.

We now see that each table takes up various amounts of space even if they contain the same number of rows. This is due to the size of the data types.

Data TypeRange Storage
big int-9,223,372,036,854,775,808 to 9,223,372,036,854,775,8078 Bytes
int-2,147,483,648 to 2,147,483,6474 Bytes
smallint-32,768 to 32,7672 Bytes
tinyint0 to 2551 Byte
Reference: Microsoft Docs

SQL Server at the most granular level stores data in data pages in the data file. Each page is 8 KB in size. SQL Server loves to cram these pages with as much data as possible. So in this example, SQL Server will need to store the bigint table across a greater number of pages compared to say the tinyint table. This is also where memory usage starts to be impacted.

Memory Usage

As discussed, SQL Server will need additional data pages to store larger data types. This is demonstrated shortly in the number of logical reads done by queries that are executed against the tables. One logical read represents one 8 KB page going into memory.

Lets test this with the following queries.

SELECT Col1, Col2, Col3 
FROM [Tinyint]

SELECT Col1, Col2, Col3 
FROM [Smallint]

SELECT Col1, Col2, Col3 
FROM [int]

SELECT Col1, Col2, Col3 
FROM [Bigint]

The same query required different amounts of RAM with the only difference being the data types of the fields in each table.

tinyint query: 17,302 logical reads x 8 = 138,416 KB = 135.17 MB of Memory Needed
smallint query: 20,409 logical reads x 8 = 163,272 KB = 159.45 MB of Memory Needed
int query: 26,596 logical reads x 8 = 212,768 KB = 207.78 MB of Memory Needed
bigint query: 39,063 logical reads x 8 = 312,504 KB = 305.18 MB of Memory Needed

Final Thoughts

Be careful when considering which data types to use when assigning them to columns. The data type you pick will matter – it could save you a lot of pain in the future. For example, if you are certain that a column is always going to have a value between 0 and 255 in any given row, consider using tinyint. These small careful decisions will add up in the years to come and save you a lot in performance.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s