SQL: Is it efficient to use tinyint instead of Integer if my max value is 255 ?

Question

Lets assume I want to save the count of datagrid rows which can be max. 24 because each row is 1 hour.

To save the row index in the database a tinyint field would be totally enough. But back in my mind I remember slightly that databases are optimized for integers?!

So is it worth to use tinyint?

Answer 1

tinyint

less space is good.

Answer 2

Tinyint is an integer, and it'd be faster than INT because TINYINT takes less bytes (1 byte) than the INT data type (4 bytes).

Reference:

• int, bigint, smallint, and tinyint (Transact-SQL)

Answer 3

With a narrower table, the database will fit more records in a single IO page, and will therefore require fewer hard disk reads.

The rule of thumb is to always use the data type that will require the least storage size.

Answer 4

Generally, less space the better, as the more rows can fit on a signle 8k I/O Page on disk (or in memory), the fewer I/Os are required to search and/or retrieve data... This is especially important for columns used in indices. However, if your machine is, for example, a 32 bit machine and is running a 32 bit OS, then the smallest memory chunk that can be independantly addressed is 32 bits, so if this is the only column in your table schema that is smaller than 32 bits, then it doesn't matter because each full row of data must start and end on a 32 bit boundary, so each row must be a multiple of 32 bits wide.

i.e., if your table was

MyTable(ColA tinyint, ColB Int, ColC DateTime) Then each row will take 16 bytes (128 bits) and 24 bits will be wasted.

On the other hand if you have 4 columns that could be tinyInts, then by all means use that as SQL server will put four of them in one 32 bit storage location on disk (no matter what order you declare them in).

Same principles apply to 64bit SQL Server running on 64bit OS/CPUbB