Path: csiph.com!x330-a1.tempe.blueboxinc.net!usenet.pasdenom.info!news.albasani.net!eternal-september.org!feeder.eternal-september.org!.POSTED!not-for-mail From: Erland Sommarskog Newsgroups: comp.databases.ms-sqlserver,microsoft.public.sqlserver.programming Subject: Re: SSE 2008: A Special Kind of Identity Date: Fri, 15 Apr 2011 23:55:04 +0200 Organization: Erland Sommarskog Lines: 53 Message-ID: References: <89bhq613oi4v5l2hfof1uslff7l0io7cjg@4ax.com> Mime-Version: 1.0 Content-Type: text/plain; charset=windows-1252 Content-Transfer-Encoding: 8bit Injection-Info: mx02.eternal-september.org; posting-host="DD6dU+BfJNjsjSP4/K/V7w"; logging-data="20374"; mail-complaints-to="abuse@eternal-september.org"; posting-account="U2FsdGVkX1+IQOuXKEsUuOKApG01w9wd" User-Agent: Xnews/2006.08.24 Mime-proxy/2.1.c.0 (Win32) Cancel-Lock: sha1:DfX+SO/w/2uGfP6JDg7bKb6go7s= Xref: x330-a1.tempe.blueboxinc.net comp.databases.ms-sqlserver:185 Gene Wirchenko (genew@ocis.net) writes: > If I have a common attribute for each transaction in a batch, I > could do something like: > create table TranBatches > ( > BatchNr int identity(1,1), > CommonAttr whoknows > ) > This common attribute might be transaction date. The transaction > table would have an FK into TranBatches. > > What if there is no common attribute? Then, all I would have is > for TranBatches is a table of numbers. This strikes me as silly. Not really. Or, there may be reason to have some more information anyway. First of all, IDENTITY may be a good choice here. In finance there is often a requierment that voucher numbers are contiguous. That is not possible to achieve with IDENTITY. In fact that's a feature of IDENTITY: having contiguous numbers means that you cannot easily have concurrent inserts. IDENTITY is good for a concurrency, because if an insertion fails or the transaction is later rolled back, the generated identity number is nevertheless rolled back. Meaning that other processes can get their IDENTITY values without waiting. Rolling your own can be done in multiple ways. With a table with a row, you can do: BEGIN TRANSACTION SELECT @nextid = coalesce(max(id), 0) FROM tbl WITH (UPDLOCK) INSERT tbl(id, ...) VALUES (@nextid, ...) -- more stuff COMMIT TRANSACTION In the system I work with, we have a few one-column one-row tables that all they do is to hold the next voucher number in some series. We also have a table which holds all voucher numbers generated in all series, and this table also holds information about which procedure that generated the number, which user and when. Good for auditing. And a good cover-up if some code mistakenly grabs a number, even if it has no work to do. -- Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se Links for SQL Server Books Online: SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx