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.
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.
|big int||-9,223,372,036,854,775,808 to 9,223,372,036,854,775,807||8 Bytes|
|int||-2,147,483,648 to 2,147,483,647||4 Bytes|
|smallint||-32,768 to 32,767||2 Bytes|
|tinyint||0 to 255||1 Byte|
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.
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
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.