Insert into myTable( myTableID)
Select max(myTableID) + 1 from myTable with (holdlock)

Should do the trick in a fairly self-contained way, no?

Juan Cristián

> -----Original Message-----
> From: Laurie Solgon [mailto:mssqldba-ezmlmshield-
> x77462977.[Email address protected]
> Sent: Thursday, April 28, 2005 11:45 AM
> To: LazyDBA Discussion
> Subject: RE: Sequential Number Creation
>
> Very true. It all depends on what you want to do with the max + 1 after
>
> you get it.
>
>
>
> -----Original Message-----
>
> From: Chris Hagemaier
>
> [mailto:mssqldba-ezmlmshield-x48517834.[Email address protected]
>
> Sent: Thursday, April 28, 2005 11:41 AM
>
> To: LazyDBA Discussion
>
> Subject: RE: Sequential Number Creation
>
>
>
> Care must be taken however. Suppose the max + 1 is selected by user1
>
> but not commited. Then suppose user2 selects the same max + 1 so
>
> that user1 and user2 have the same number. If they both committed
>
> then the app would attempt to insert identical numbers into the
>
> target table (which may be good or bad depending on what you're
>
> trying to do).
>
>
>
> We wrote such an inhouse app sveral years ago (in Oracle but so what).
>
> We didn't select the max+1 until a final commit and even then it was
>
> possible for two clerks accepting payments at the counter to get the
>
> same receipt number. So we constructed a table of sequence values (one
>
> column only with a unique constraint (index)). When max+1 was selected
>
> it was immediately inserted back into the sequence table and then all
>
> work was commited. If two users attempted an insert of the same number
>
> one of them would violate the unique constraint, the app would catch it
>
> and deal with it. And....... it worked. Just something to consider.
>
>
>
> -----Original Message-----
>
> From: Laurie Solgon
>
> [mailto:mssqldba-ezmlmshield-x70238726.[Email address protected]
>
> Sent: Thursday, April 28, 2005 11:24 AM
>
> To: LazyDBA Discussion
>
> Subject: RE: Sequential Number Creation
>
>
>
> Select max(numb_field) + 1
>
>
>
>
>
>
>
> This can be used to insert later.
>
>
>
>
>
>
>
> -----Original Message-----
>
>
>
> From: Hornak Wayne
>
>
>
> [mailto:mssqldba-ezmlmshield-x84458119.[Email address protected]
>
>
>
> Sent: Thursday, April 28, 2005 11:13 AM
>
>
>
> To: LazyDBA Discussion
>
>
>
> Subject: Sequential Number Creation
>
>
>
>
>
>
>
> How do I create a unique sequential number within the result of a sql
>
>
>
> statement?
>
>
>
>
>
>
>
>
>
>
>
> ---------------------------------------------------------------------
>
>
>
> TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
>
>
>
> Website : http://www.LazyDBA.com
>
>
>
> To unsubscribe: http://www.lazydba.com/unsubscribe.html
>
>
>
> For additional commands, e-mail: mssqldba-[Email address protected]
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> This message and any attachments are solely for the intended recipient
>
> and may contain confidential or privileged information. If you are not
>
> the intended recipient, any disclosure, copying, use, or distribution of
>
> the information included in this message and any attachments is
>
> prohibited. If you have received this communication in error, please
>
> notify us by reply e-mail and immediately and permanently delete this
>
> message and any attachments. Thank you.
>
>
>
> ---------------------------------------------------------------------
>
> TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY Website :
>
> http://www.LazyDBA.com To unsubscribe:
>
> http://www.lazydba.com/unsubscribe.html
>
> For additional commands, e-mail: mssqldba-[Email address protected]
>
>
>
>
>
>
>
> --
>
> This message has been scanned for viruses and dangerous content by
>
> MailScanner, and is believed to be clean.
>
>
>
> Douglas County, Oregon
>
> www.co.douglas.or.us
>
>
>
>
>
>
>
> ---------------------------------------------------------------------
>
> TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
>
> Website : http://www.LazyDBA.com
>
> To unsubscribe: http://www.lazydba.com/unsubscribe.html
>
> For additional commands, e-mail: mssqldba-[Email address protected]
>
>
>
>
>
>
>
> This message and any attachments are solely for the intended recipient and
> may contain confidential or privileged information. If you are not the
> intended recipient, any disclosure, copying, use, or distribution of the
> information included in this message and any attachments is prohibited. If
> you have received this communication in error, please notify us by reply
> e-mail and immediately and permanently delete this message and any
> attachments. Thank you.
>
> ---------------------------------------------------------------------
> TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
> Website : http://www.LazyDBA.com
> To unsubscribe: http://www.lazydba.com/unsubscribe.html
> For additional commands, e-mail: mssqldba-[Email address protected]


MS Sql Server LazyDBA home page