Getting next number

All,
Without using identity and assume i don't care if i have holes in my
numbers, is this the best way to get and set the next number?
update NUMID set @.x = NUMID.CS_ID_NUM = NUMID.CS_ID_NUM + 1 from numid
(nolock)
SteveAn update always obtains an exclusive lock on every row that is updated.
The only hint that is applicable here is WITH(ROWLOCK).
UPDATE NUMID WITH(ROWLOCK) SET @.X = CS_ID_NUM = CS_ID_NUM + 1
"Steve Drake" <Steve@._NOSPAM_.Drakey.co.uk> wrote in message
news:udPNucYoFHA.568@.TK2MSFTNGP10.phx.gbl...
> All,
> Without using identity and assume i don't care if i have holes in my
> numbers, is this the best way to get and set the next number?
> update NUMID set @.x = NUMID.CS_ID_NUM = NUMID.CS_ID_NUM + 1 from numid
> (nolock)
> Steve
>|||Wouldn't this just increment each CS_ID_NUM by 1, is that what you want to
do?
Dan.
"Brian Selzer" <brian@.selzer-software.com> wrote in message
news:eIvyelYoFHA.632@.tk2msftngp13.phx.gbl...
> An update always obtains an exclusive lock on every row that is updated.
> The only hint that is applicable here is WITH(ROWLOCK).
> UPDATE NUMID WITH(ROWLOCK) SET @.X = CS_ID_NUM = CS_ID_NUM + 1
> "Steve Drake" <Steve@._NOSPAM_.Drakey.co.uk> wrote in message
> news:udPNucYoFHA.568@.TK2MSFTNGP10.phx.gbl...
>|||> Without using identity
Can you explain why?

> update NUMID set @.x = NUMID.CS_ID_NUM = NUMID.CS_ID_NUM + 1 from numid
> (nolock)
You can't use (nolock) here, this is a hint for retrieving data, and is not
applicable for an update (which needs to lock the row(s), for obvious
reasons).
If you are trying to avoid IDENTITY, I assume you are trying to avoid
anything that won't port, so how about the unproprietary:
BEGIN TRAN
SELECT @.x = MAX(CS_ID_NUM)+1 FROM NUMID
UPDATE NUMID SET CS_ID_NUM = @.x
COMMIT TRAN
I assume NUMID only has one row, you may consider a better design if there
are multiple columns, e.g.
CREATE TABLE NumIDs
(
NumType VARCHAR(32) PRIMARY KEY CLUSTERED,
NumValue INT - or BIGINT
)
Now you can insert a row for each number type you have, and there will be
less contention on the table because only that row has to be locked when
getting the next value.|||Aaron,
The unproprietary solution below is fraught with errors. Nothing prevents
another transaction from obtaining the same maximum value and attempting to
update the table with it. Worse yet, if the load on the server is heavy
enough, it is possible for several other transactions to update the table
between the select statement and the update statement. Statement execution
order is only deterministic within the same plan. The order in which
statements from different plans are executed depends on the load on each
processor, and thus cannot be determined with any acceptable level of
certainty. Using SELECT MAX to find the next number is a common error
usually committed by neophyte SQL Server developers. I'm surprised to see
it suggested by an MVP. I think you need to add WITH(UPDLOCK, HOLDLOCK) to
apply an update range-lock which is the only way to prevent both inserts
between the select and the update and deadlocks caused by shared locks being
held by another transaction while it's waiting to perform its update.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:etNALXZoFHA.1416@.TK2MSFTNGP09.phx.gbl...
> Can you explain why?
>
> You can't use (nolock) here, this is a hint for retrieving data, and is
not
> applicable for an update (which needs to lock the row(s), for obvious
> reasons).
> If you are trying to avoid IDENTITY, I assume you are trying to avoid
> anything that won't port, so how about the unproprietary:
> BEGIN TRAN
> SELECT @.x = MAX(CS_ID_NUM)+1 FROM NUMID
> UPDATE NUMID SET CS_ID_NUM = @.x
> COMMIT TRAN
> I assume NUMID only has one row, you may consider a better design if there
> are multiple columns, e.g.
> CREATE TABLE NumIDs
> (
> NumType VARCHAR(32) PRIMARY KEY CLUSTERED,
> NumValue INT - or BIGINT
> )
> Now you can insert a row for each number type you have, and there will be
> less contention on the table because only that row has to be locked when
> getting the next value.
>|||Whoa, whoa, whoa.
First of all, I asked why on earth they would be doing this anyway, instead
of IDENTITY.
Second, yes, if you apply the correct isolation level, you can certainly
achieve it the way I suggested. (I'm very sorry I displeased you by
forgetting that line.)
Third, I certainly don't think this confusing syntax (UPDATE NUMID
WITH(ROWLOCK) SET @.X = CS_ID_NUM = CS_ID_NUM + 1) is any better a solution.
YMMV.
Fourth, when you're going to attack and demean someone in this way, it would
be appreciated if you do so in private mail.
A
"Brian Selzer" <brian@.selzer-software.com> wrote in message
news:OXTIDxZoFHA.3068@.TK2MSFTNGP15.phx.gbl...
> Aaron,
> The unproprietary solution below is fraught with errors. Nothing prevents
> another transaction from obtaining the same maximum value and attempting
> to
> update the table with it. Worse yet, if the load on the server is heavy
> enough, it is possible for several other transactions to update the table
> between the select statement and the update statement. Statement
> execution
> order is only deterministic within the same plan. The order in which
> statements from different plans are executed depends on the load on each
> processor, and thus cannot be determined with any acceptable level of
> certainty. Using SELECT MAX to find the next number is a common error
> usually committed by neophyte SQL Server developers. I'm surprised to see
> it suggested by an MVP. I think you need to add WITH(UPDLOCK, HOLDLOCK)
> to
> apply an update range-lock which is the only way to prevent both inserts
> between the select and the update and deadlocks caused by shared locks
> being
> held by another transaction while it's waiting to perform its update.|||(1) I totally agree with you that IDENTITY is always a better solution than
rolling your own autonumber mechanism.
(2) Setting the isolation level is not enough, because two transactions
executing the same procedure can obtain shared locks on the same resource
during the select statement, and then deadlock on the update statement. If
you use SELECT MAX, then you have to use WITH(UPDLOCK, HOLDLOCK) to
serialize the select/update pairs as a unit to prevents this kind of
deadlock.
(3) The confusing syntax will not incur the problems caused by SELECT MAX
because the update statement will obtain an exclusive lock on the row while
it's being updated, and @.x will thus be different every time the statement
executes no matter how many concurrent users issue the statement. On the
other hand, I agree that this is not a good substitute for IDENTITY, because
it can also cause blocking and complicate deadlock avoidance if more than
one procedure can insert rows into the same table.
(4) I apologize for the neophyte remark. That was uncalled for. I think
it's necessary, however, to point out problems with code in public--rather
than leave it unchallenged--to save people with less experience the hassle
of finding the bugs the hard way, especially the kind that manifest
intermittently which as you know are the toughest to track down.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uTJuJ6ZoFHA.3936@.TK2MSFTNGP10.phx.gbl...
> Whoa, whoa, whoa.
> First of all, I asked why on earth they would be doing this anyway,
instead
> of IDENTITY.
> Second, yes, if you apply the correct isolation level, you can certainly
> achieve it the way I suggested. (I'm very sorry I displeased you by
> forgetting that line.)
> Third, I certainly don't think this confusing syntax (UPDATE NUMID
> WITH(ROWLOCK) SET @.X = CS_ID_NUM = CS_ID_NUM + 1) is any better a
solution.
> YMMV.
> Fourth, when you're going to attack and demean someone in this way, it
would
> be appreciated if you do so in private mail.
> A
>
>
> "Brian Selzer" <brian@.selzer-software.com> wrote in message
> news:OXTIDxZoFHA.3068@.TK2MSFTNGP15.phx.gbl...
prevents
table
see
>|||> it's necessary, however, to point out problems with code in public--rather
> than leave it unchallenged--
Pointing it out to me in private would have allowed me the courtesy to
correct myself, instead of defending myself. Do you really think that if
you pointed it out to me that I would have ignored it? Instead I'm called a
neophyte in front of everyone... yeah, you're right, I should prefer your
methodology. :-(|||I did not set out this morning to embarass you. I assumed that you would
simply post "Whoops, brain freeze! Should've waited 'til I had my morning
coffee." and let it go at that. Instead, you decided to take offense. That
is your perogative. It is also my perogative to respond thus: Waaaah!!!
You chose to decorate your name with [SQL Server MVP] so that more people
will read and heed your advice. Are you so insecure that you must take
offense when someone publicly points out an incorrect or incomplete
response? Personally, I have no problem admitting that I'm not omniscient
or otherwise godlike (at least not until I've had my morning coffee): the
message I sent this morning to ten.xoc@.dnartreb.noraa bounced.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uebvT3aoFHA.3912@.TK2MSFTNGP10.phx.gbl...
public--rather
> Pointing it out to me in private would have allowed me the courtesy to
> correct myself, instead of defending myself. Do you really think that if
> you pointed it out to me that I would have ignored it? Instead I'm called
a
> neophyte in front of everyone... yeah, you're right, I should prefer your
> methodology. :-(
>|||> is your perogative. It is also my perogative to respond thus: Waaaah!!!
Ok, we're going to be mature about this; I'll remember that, all I was
asking for was a little courtesy. And my e-mail address is reversed to
thwart spam; hundreds of other people have managed to figure it out, I'm
sorry that you couldn't.