Need pkey data type of 32 < x < 64 bits size.

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: jan (anomalocarus_at_hotmail.com)
Date: 03/22/04 

Date: 22 Mar 2004 05:39:32 -0800

Hi all,
We have a database with 32 bit object identifiers. As we expect this
to grow considerably we're worried that we may run out of this
'address' space, but the obvious alternative of a 64 bit bigint is not
something I'm comfortable with as these identifiers are pervasive -
the idea of adding an extra four bytes on to just about every row in
our database is something I'm very unhappy about; we really expect to
be scaling very large indeed.
If I could find something of 40 bits I think that would do us for the
moment, but I can't find anything suitable. A char(5) is superficially
okay but the SQL standard method of string comparison, where trailing
spaces are effectively ignored, might have some unexpected surprises,
and I'm not sure how to efficiently get the next one up from the
current one; something which is obviously very easy to do with
integers.
I had to experimented with decimal (9, 0) but while this takes five
bytes, which is good, it will take nine decimal digits at the most,
which is not so good. I presume this is BCD under the skin.
I could of course add an extra column, a tinyint, but that puts an
extra load on the SQL optimiser, and frankly, gives it an extra chance
to make a mistake (I've had to track down a few bad query plans).
Any suggestions would be welcome.

By the way what is the difference between binary and varbinary? if
char and varchar can represent the entire range of Ascii (and I
thought it did) what it is the point of their binary counterparts?

cheers

jan

Relevant Pages