On Thursday 15 June 2006 6:38 pm, Daniel Noll wrote: > Michael Segel wrote: > > Uhm, > > > > I'm sorry, but where did I ever say 'select max()'? > > Hint: I didn't because its not that efficient. > > Presumably he column would be indexed if you're doing operations like > max() on it anyway, so efficiency isn't the problem. I suspect the > problem would be what to do when two users tried to insert rows at > around the same time. The second would fail to commit the transaction > and have to try again. > > Daniel No. First, you're still returning a value from the sequence that will cause an exception. So you have overhead. Also you'd have a lot harder time trying to manage the inserts. Also, you have to consider that its cheaper to get a value from memory than it is to apply the max() function on a value from the index.
Again the easiest way is to update the sequence when you have a successful insert where there is a value in the identity column greater than the current base value. Everything is in memory and of course you'll have to deal with a mutex lock on the sequence while you're doing the update. again pretty trivial or rather straight forward....
