Using Decimal as a Key

[rottytooth]

Is there any disadvantages of using Decimal type as a primary key? If
so, are there other data types that can hold large numbers (more than
an int can hold: 13 digits or so) and perform better as a Key field?

Thanks.

[Roy Harvey]

I would expect bigint to be a slightly better choice than decimal. It
stores in 8 bytes, while a decimal of 10-19 digits takes up 9.

Roy Harvey
Beacon Falls, CT

On 11 Jul 2006 12:12:27 -0700, "rottytooth" <ro********@gmail.com>
wrote:

>Is there any disadvantages of using Decimal type as a primary key? If
so, are there other data types that can hold large numbers (more than
an int can hold: 13 digits or so) and perform better as a Key field?

Thanks.

[rottytooth]

Looks like bigint holds enough for my purposes. Thanks.

[--CELKO--]

>Is there any disadvantages of using Decimal type as a primary key? If so, are there other data types that can hold large numbers (more than an int can hold: 13 digits or so) and perform better as a Key field [sic] ? <<

This question is like "What car should I buy?"

Look at the nature of the data from a logical view point. Is it fixed
length? Are there constraints on it? Your insanely vague pseudo-specs
are useless. And you don't know that a column and field re totally
different concepts, which is part of why you don't know how to give a
spec.

For a key, you should be using an industry standard code whenever
possible -- say a VIN, ISBN, UPC, EAN, etc. If this industry standard
is expressed as a decimal (ICD, Dewey Classification, etc.) then use
DECIMAL(s,p) or NUMERIC(s,p).

The worst thing you could is grab an absurd data type like FLOAT or
BIGINT to invite garbage and in the case of BIGINT prevent portable
code. CHAR(n) is a good choice for a hierarchical encoding.