Re: SQL Insert Row as last RRN

On 12-Sep-2011 12:07 , Morgan, Paul wrote:

IDENTITY column requires maintaining a sequence in the database with
some minor performance issues especially with multiple processes
using the same table.

If ["multiple processes" suggests] concurrency is already an issue for the OP, then the RRN implementation already seems flawed, even if reuse of deleted rows is prevented.? The DELETE in an earlier post was using a RANGE on RRN() values, so if competing insert requests can intersperse... Ouch!

From what I have heard about using SQL with RRN() ordering, I think there would be a performance benefit for using an identity instead. Using identity eliminates any requirement to turn off reuse of deleted rows and more importantly eliminates entirely any use of the RRN(). Using RRN() vitiates the choice of the SQL, forcing the SQL to function like just another RLA method when many of the benefits from the SQL are derived from not being dependent on RRN().

The column sequence could require a reset. It will eventually wrap
around to a lower sequence and throw off your ordering when it does
wrap. It's a similar problem you are having with the RRN.

The AS IDENTITY clause can specify NO CYCLE to prevent wrapping, except when wrapping is the effect of a user-requested reset to the identity column [start with] attributes. I do not know what sqlstate identifies that an insert exceeded the MINVALUE or MAXVALUE.

Regards, Chuck