97

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?

Improve this question
7
  • 2
    This is a very nice quesiton (+1). MySQL has SELECT ... PROCEDURE ANALYSE() which actually recommends the smallest data types the table should have for the given SELECT. That was partly the inspiration behind my answer.
    – RolandoMySQLDBA
    Aug 23, 2011 at 15:47
  • 4
    Fine question, but to precise the tinyint range is 0-255. The bit field is 0 or 1 (or NULL). Storage cost for a tinyint is 1 byte. Every 8 bit fields in a table will cost 1 byte of storage. msdn.microsoft.com/en-us/library/ms187745.aspx and msdn.microsoft.com/en-us/library/ms177603.aspx
    – billinkc
    Aug 24, 2011 at 11:41
  • @billinkc Right. That is why I mentioned the possibility expanding the column to include the values 2 or 3. If you include 2 or 3, you have to use tinyint (at the very smallest scale).
    – Richard
    Aug 24, 2011 at 11:45
  • 1
    "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)." I'd use an ENUM for such a thing. These are stored as bitfields, and as many others have pointed out here, small savings per record add up to large savings over the entire database -- even moreso if the column is indexed.
    – user6655
    Feb 13, 2012 at 18:17
  • 3
    @user6665 I'd use an ENUM for such a thing. Not in SQL Server, you wouldn't, since it doesn't have enumerations of any kind.
    – underscore_d
    Nov 3, 2016 at 19:27

5 Answers 5

Reset to default
100

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.

Improve this answer
0
30

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

Improve this answer
3
  • 5
    Good point. An absolute worst case example being a 4028 byte row consisting of entirely fixed length columns that you want to add a column to. Adding a smallint would take you to 4030 (2 rows per page) but an int pushes you over the boundary (1 row per page, 4028 bytes wasted per page).
    – Mark Storey-Smith
    Aug 23, 2011 at 19:28
  • I once did a performance test on int vs bigint. Saving 1 million records, comparing time and storage, and retrieve them one by one, again measuring performance. I didn't see major differences. I'm going to do the same performance test for int vs tinyint. I really think it can be neglected for 80% of the applications, resulting in more consistent data types, and lesser maintenance costs.
    – Saeed Neamati
    Jan 12, 2016 at 7:03
  • 1
    @SaeedNeamati You might want to reread the article from Mark's answer ("Have you ever heard... let's just get this done – we'll worry about performance later?... I hear this all the time…") and gbn's here. I think the take home is that any inefficient choice is going to show its stripes at the right scale, and OP's gut isn't wrong.
    – ruffin
    Nov 9, 2017 at 15:22
14

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.

Improve this answer
14

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.

Improve this answer
9

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.

Improve this answer
3
  • 3
    This is an older thread, but clarification and validation are not the only reason. If you have VARCHAR(4000) for something that should be VARCHAR(20) the query plan will think that your memory and CPU requirements are many multiples of what they should be as regards that column. I've not taken the time to do this, but I'm guessing that you can probably see this by looking at a query plan for VARCHAR(20) and then change to VARCHAR(4000) and check estimated costs.
    – user41646
    Jun 25, 2014 at 16:39
  • 3
    @GeorgeShouse Demonstration of that here
    – Martin Smith
    Jun 25, 2014 at 21:07
  • Also, varchar ≤ 255 uses less space on the row.
    – PRMan
    Jul 27, 2020 at 18:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.