T-SQL Tuesday #136 – My Favorite Data Type

Introduction

This month’s T-SQL Tuesday is being hosted by Brent Ozar (blog|twitter). The subject is what is your favorite or least favorite data type. As Brent describes it, “Your mission: write a blog post about your favorite data type“. This post will be my contribution for T-SQL Tuesday #136 – My Favorite Data Type.

This is a somewhat more focused subject area than usual, that still gives everyone plenty of possibilities for blog topics. This is good, since I think it will encourage more people to actually write a blog post! Just writing a useful blog post, and getting quality links back and forth from other blogs in the SQL Server community is good for everyone who participates!

BTW, reading and then commenting on the T-SQL Tuesday blog posts of other participants helps both you and the other blogger. They get views and comments on their blog, and you get backlinks to your blog. T-SQL Tuesday also helps encourage people to blog more often, which is good.

In my case, I am going to talk about my favorite data types and also a little about the SQL Server Data Compression feature.

T-SQL Tuesday #136 – My Favorite Data Type

Here are my favorite data types.

My Favorite Data Type

My favorite group of data types are the integer data types. These are exact-number data types that use integer data, and there are four choices available in SQL Server. Here are the choices, with the range of values they can hold.

Data Type	Data Range
tinyint	0-255
smallint	-32,768 to 32,767
int	-2,147,483,648 to 2,147,483,647
bigint	-9,223,372,036,854,775,808 to 9,223,372,036,854,775,807

The int data type is the most commonly used of these four types, especially when non-DBAs are designing database schemas. One advantage of the int data type is that it is quite flexible, holding values from -2,147,483,648 to 2,147,483,647 while requiring four bytes of storage. Here is how much space is required to hold each data type.

Data Type	Storage Space
tinyint	1 byte of storage
smallint	2 bytes of storage
int	4 bytes of storage
bigint	8 bytes of storage

Just Use an int…

In reality, int is often used when you could actually get away with using a smallint or even a tinyint data type. I think many people either don’t know or simply forget that there are other choices beside int. It is also common that many DBAs and developers don’t have good requirements or use cases available when they develop their data models and do their physical design.

As a result, they may guess about the range of values they need to store, and end up selecting int, “just to be safe”. This ends up making your data take more space in your data file(s) on disk. It also requires more work from your storage subsystem to read your data, and more space in the buffer pool to keep it in memory.

I think you should do the best that you can to select the “smallest” data type possible to hold your data, based on your business requirements. Don’t just use int without even thinking about it!

SQL Server Data Compression

SQL Server Data Compression was first introduced in SQL Server 2008, and it was improved in SQL Server 2008 R2. Unfortunately, it was an Enterprise Edition only feature until SQL Server 2016 SP1.

This feature lets you compress individual row store indexes with either ROW or PAGE compression. Data compression can be a huge win for many SQL Server workloads. It can reduce both your required disk I/O and your memory usage at the cost of some added CPU usage in some scenarios.

If you have any large, relatively static tables with data that compresses well, data compression can be very beneficial. It seems to still be an underutilized feature that could be worth your time to investigate.

My recent video has some useful queries that can help you decide whether data compression is a good choice for the indexes in a particular table.

I also have a recent post that talks a little more about SQL Server Data Compression and how to decide whether it makes sense for your data and workload.

• Estimating Data Compression Savings in SQL Server

Final Words

I hope that this post has been interesting and useful! I also want to thank Brent for hosting this month.

If you have any questions about this post, please ask me here in the comments or on Twitter. I am pretty active on Twitter as GlennAlanBerry. Thanks for reading!