Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
1  
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 '11 at 15:47
2  
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 '11 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 '11 at 11:45
    
Indeed you did, I blame a lack of tea for comprehension –  billinkc Aug 24 '11 at 12:48
    
"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 '12 at 18:17
add comment

5 Answers 5

up vote 51 down vote accepted

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.

share|improve this answer
2  
Very nice link. That's what I was thinking, too. I don't care too much about disk space. It's everything else that I was wondering about. –  Richard Aug 23 '11 at 15:29
    
Ditto on Richard's Comment, and +1 for you Mark. –  RolandoMySQLDBA Aug 23 '11 at 15:49
    
Well, I tried. I guess we need to be babysat in there. –  Aaron Bertrand Apr 1 '13 at 22:22
add comment

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

share|improve this answer
2  
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 '11 at 19:28
add comment

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.

share|improve this answer
add comment

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 - 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 bigger 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.

share|improve this answer
add comment

While there are already many great answers let me throw in me two cents.

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 if 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.

And this very same reasons apply to tinyint as well.

share|improve this answer
    
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. –  George Shouse Jun 25 at 16:39
    
@GeorgeShouse Demonstration of that here –  Martin Smith Jun 25 at 21:07
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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