Integers and Performance

[agoeddeke]

Is there a performance difference by using an Integer key instead of say a Char(4), Smallint or Tinyint? I'm wondering if Integers have any other performance value other than the compressed 4 bytes of space they take up.

For instance... will a join perform any better with an Integer key as opposed to a Char(4) key? Will selecting on a value (...WHERE mykey=2) perform better with an Integer?

Also, if you know the value of a key won't exceed 32,000 would it be best to simply make the key a Smallint rather than an Integer?

Thanks.

 

[vongrunt]

For instance... will a join perform any better with an Integer key as opposed to a Char(4) key? Will selecting on a value (...WHERE mykey=2) perform better with an Integer?

Question: how to store 2 billion into char(4)? And preferably turn off collation/coercion rules?

Otherwise I'd guess that integer is faster... but there are many layers between CPU and application and everything finally ends on a sloooow hard disk so I don't think this matters so much as 10 years ago.

Also, if you know the value of a key won't exceed 32,000 would it be best to simply make the key a Smallint rather than an Integer?

Definitely. Less disk space -> less server I/O.

 

[agoeddeke]

Thanks for you comments. I realize there are limitations with other data types, but wanted to make sure there was no extra "behind the scenes" magic using integer keys other than the obvious space consideration.