Int vs tinyint

Last Post 25 Jul 2006 10:26 AM by dbandee. 3 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
New Member
New Member

25 Jul 2006 09:18 AM
Just 2 quick questions about numeric data types, as used for primary keys.

Part 1 - I am working on a database with a number of lookup tables that have 20 records or less. They all have numeric primary keys, but some use integers and some use tinyints for these fields. If the maximum number of records that could ever be in any of these tables is less than 100, is there any particular reason why we should be using an integer instead of a tinyint for these? (Some have identity columns, and some don't).

Part 2 - I think in Access, you need to have identity columns defined as long integers (equivalent to integer in SQL Server). If some users will be accessing the database with Access, will they have problems with these tables if the keys are not defined in SQL Server as integers? Or will it not matter since the SQL Server engine is what will be ultimately adding & updating records?

Thanks for any thoughts or advice on this.
New Member
New Member

25 Jul 2006 10:26 AM
it takes l1 byte to store tinyint value vs 4 bytes for integer.
New Member
New Member

02 Oct 2006 10:50 AM
I prefer to use smallint for all of my lookup tables with 130,000 or fewer rows. I used to use a mix of tinyint and smallint and it became a pain when I did the UI development. I kept having to look to see what the datatype was. I figure use smallint for everything you can and don't use tinyint. We're talking a difference of 1 byte of storage so at the most I would be wasting 130KB of storage. When a 250GB hard drives is under $100 I'm not worried about the 130KB.
New Member
New Member

06 Oct 2006 05:58 AM
Naturally, you should make the column as thin as possible.

I ran into some trouble (indexing or constraint or other.. can't recall) with tinyint.

So make sure you test it thoroughly.

The key to high performance tables are:

Thin columns: tinyint, smalldatetime, smallmoney, etc.

No varchars - put them into a 1-to-1 table if you need them.

Kalman Toth, Database, Data Warehouse and BI Architect
The Best SQL Server 2005 Training in the World

Acceptable Use Policy