Re: automatically calculating ID field on INSERT

Joe Celko wrote:

> UPDATE HighestId
> SET id = id + CAST (RANDOM(id) * 100 AS INTEGER);

Since you seem to be attempting to distribute your surrogate key values, I was wondering if anyone had utilized an algorithmic approach. The formula below is intended to distribute key values evenly throughout a 31bit address space (the 31bit alg. is simpler that the full 32bit range and needn't worry about overflow).

UPDATE id_table
SET id = id/2+mod(mod(id,2)+mod(id/8,2),2)*2**30;

SELECT id FROM id_table;

(from "Key Points About Surrogate Keys" Roy Hann)

While the formula looks ugly to a human, a quick benchmark indicates that the calculation overhead is negligible even on a small pc.

--
Shane