When to use TINYINT over INT?

Question

In general, I always use Ints. I know that in theory this is not the best practice, though, since you should use the smallest data type that will be guaranteed to store the data.

For example, it's better to use tinyint when you know that the only data you will store is a 1, 0 or null (with a very small chance of expanding that to a 2 or 3 later).

However, the only reason I know for doing this is for storage purposes--using 1 byte on a row instead of 4 bytes.

What are the impacts of using tinyint (or smallint or even bigint) over just int, other than saving space on your hard drive?

Answer 1

Disk space is cheap... that's not the point!

Stop thinking in terms of storage space, think instead about buffer pool and storage bandwidth. At the extreme end, CPU cache and memory bus bandwidth. The linked article is part of the series highlighting issues with poor clustered key selection (INT vs GUID vs Sequential GUID) but it highlights the difference bytes can make.

The overriding message is design matters. The difference won't show up in an individual database on an appropriately spec'd server until you hit VLDB territory but if you can save a few bytes, why not do so.

I'm reminded of the environment described in an earlier question. 400+ databases, ranging in size from 50mb-50GB, per SQL instance. Scrubbing a few bytes per record, per table, per database across that environment could make a significant difference.

Answer 2

In addition to the other answers...

Rows and index entries are stored in 8k pages. So a million rows at 3 bytes per row isn't 3 MB on disk: it affects the number of rows per page ("page density").

The same applies to nvarchar to varchar, smalldatetime to datetime, int to tinyint etc

Edit, June 2013

http://sqlblog.com/blogs/joe_chang/archive/2013/06/16/load-test-manifesto.aspx

This article states

The important criteria are the cardinality and the page to row ratio.

So, choice of data type does matter

Answer 3

It's not only table storage that is a consideration. If you use indexes where the int column is part of a compound key, you would naturally want the index pages as full as possible, this being the result of index entries being as small as possible.

I would definitely expect to find that examining index entries in BTREE pages would be a little faster with smaller data types. However, any VARCHARs involved in index entries would offset (nullify) performance gains from using TINYINT over INT.

Notwithstanding, if index entries have compound entries and all are integers, the smaller the integers are bytewise, the better and the faster.

Answer 4

All things become gain complexity when databases gets bigger:

• maintenance windows needs to be enlarged or rescheduled
• backups (the end-of-day full backup becomes an absurd time-eater, so you need an differential or even log backups and do the full once-a-week, maybe once-a-month)
• performances maintanances becomes an time-eater (creating an index on a multi-million-row table takes not trivial time to execute) and needs to be rescheduled and gets worse if the table is wide...
• And transmitting that 100Gb backup through the network is not what I call an piece of cake - specially if the network (for some unknown reason) is stubborn on dropping the connection on the 75Gb mark... (happened with an installation I was working that was backuping to an mapped drive on the network)...

And what datatypes have to do with that? EVERYTHING. Using row sizes bigger than necessary makes database pages fill before than needed or even wasting space if the row size is such that no more than one record is able to be recorded on the page. The result is more pages needed to written and readed, more RAM memory is used to cache that (bigger records needs more memory). And since your datatypes are specified bigger than needed from disk, your indexes will suffer the same problem - specially if you cluster that composite 2 BIGINT columns primary key since any other indexes created will copy that primary key implicitly on their definition.

If you know that some columns in a table that will have millions of row or even an little table that will FK'ed to multi-million-row that doesn't need an 4 bytes integer to store their data, but an 2 byte would suffice - use SMALLINT. If values in the range 0-255 is enough, TINYINT. An Yes/No flag? There's BIT.

Answer 5

While for tinyint vs int there are clear differences such as disk space, page splits and maintenance time, there wouldn't be any of these for varchar.

So why not declare all text fields as varchar(4000), since it will anyway use up only the space needed? Even more you will be guaranteed that your data will never be truncated.

The answer is of course:

1. Clarification of your intentions (as nobody will understand why a name field should be 4000 characters)
2. Validation as you want to make sure nobody enters an entire biography as the name.

These very same reasons apply to tinyint as well.