Use the smallest SQL Server datatypes for great performance

4/2/2011 1:59:52 PM

A lookup table with 10 records can have a PK of int or tinyint. Thats 3 bytes in difference. A 1 million record table referencing that table will then use either 4,000,000 or 1,000,000 bytes. That column will probable be part in an index. That index tree will be bigger and take more time to process, and using more RAM and CPU. Query will take longer and tables are locked a longer time. It is easy to choose a smaller datatype and you will gain much performance from doing so.

Cite "If you have a table that will be limited in how many rows it will ever have, such as a lookup table of statuses, use a TINYINT instead of INT for the ID field.

Those 3 bytes don't seem like much in the lookup table itself as there might only be 10 rows for a total of 30 bytes, but keep in mind that as a lookup table the purpose of the ID field is to be stored in at least one if not more other tables that can be quite large. The impact of those 3 bytes when that field is part of several multi-million row tables is much more noticeable."

Read this article from Solomon Rutzky in Disk Is Cheap! ORLY?

Here's some posts regarding this matter as well.. stackoverflow >>

Also, this aticle sums the data types up, SQL Server Data Types Reference