It seems that when I'mmaking an ID

[david7777777777]

It seems that when I'mmaking an ID column in my tables, the INT data type is the one that gets used, based on what I've read or what SQL prefers. What are your thoughts on the data type for an ID field? Does it really matter what the data type is?

 

[tlbroadbent]

Per SQL Books Online:

"The IDENTITY property can be assigned to tinyint, smallint, int, bigint, decimal(p,0), or numeric(p,0) columns."

In other words you can use exact numeric data types that can be defined to hold INTEGER values. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[TheInsider]

No, it doesn't matter what type of field it is. You should select a data type that best suits your situation. For example you could use a tinyint (1 byte) to represent the rows in a table that holds the names of the US states because a tinyint has a range of 0 to 255, and there are only 50 states. This is the most efficient way in terms of storage space because, other than the bit data type, you can't get smaller than a tinyint. However, the tinyint would not be suited for keying records in a phone book table because a given city could have 1 million residents. Here you would need a data type with a larger range so you don't run out of numbers.

Basically you should look at the range of numbers that any given data type supports and compare that to the maximum number of records that you expect the table to hold. Then choose the data type that requires the smallest number of bytes to store while still being able to supply numbers for your maximum scenario.