SQL for Smarties | SQL Programming Style | Trees and Hierarchies in SQL | SQL Puzzles and Answers | Data and Databases

Monday, May 29, 2006

Get last inserted PK

SQL Apprentice Question
I have a table that has a computed value in the primary key (int column).
The computed expression is below:

(convert(int,(rand() * power(2,30))))

Does anyone know how I can get the last inserted value on this table since I
can't use @@IDENTITY. Thanks.

Celko Answers
What you have will fail because of duplication. Use additive congrunce instead.

http://www.rationalcommerce.com/resources/keys.htm http://www.rationalcommerce.com/resources/surrogates.htm http://www.rationalcommerce.com/resources/lfsr.gif

The formula is easy for a 31-bit number, always gives a unique answer and you will have the last number in a one-row table that will stay in main storage.

No comments: