Indentity columns'' data type

Question

Indentity columns'' data type

lindss01

SSCrazy

Points: 2264
More actions
June 2, 2005 at 7:18 am

#110240

In SQL Server 2000,
What is the best data type for Identity Columns? The tables I have been looking at have NUMERIC(18,0). I think BIGINT or even INT would be better. What do you think? and why? Are there any references.

Thanks for the info,
Steve

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

The smaller the column, the better. The real question is how many records do you expect to have? If you think you'll go well over 3 billion (int goes up to 4.2? B)), then you might consider the bigint... if not stick to the int.

Frank Kalis SSC Guru Points: 111183 More actions · Answer 2

BOL is a very good and handy reference.

Currently, you have DECIMAL(18,0) which is at 9 bytes per row. Even BIGINT is only at 8 bytes. The most compact here is INT at 4 bytes, which is also the most commonly used type, I believe.

The more compact a data type, the more rows fits on a page, thus fewer IO is needed.... and so on.

If you can expect the tables to be below 2,1 billion rows choose INT. If you know in advance that you will go beyond this limitation, choose BIGINT. If you are about to run out of the scope of an INT, you can change the underlying data type to BINGINT at any time anyway.

And before you intervene Remi, I am aware that you of course can start at the negative max value for an INT as the seed value, which gives you potentially some 4,2 billion values. But the chance that someone really does this in production are very small.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

Am I the kind of fuy that intervenes??

Obviously.... When I wrote that I just remembered that you had 4+ billion values... forgot that half of them had to be negative .

The point still stands, start with int unless you're certain you'll blow past 2B. But as Frank stated, you can always change that type later on. It becomes a bigger task if this column is used in a primary key and referrenced by multiple foreign keys... but then again it's not so hard to correct.