Groups | Search | Server Info | Keyboard shortcuts | Login | Register

Groups > comp.databases.ms-sqlserver > #185

Re: SSE 2008: A Special Kind of Identity

Cross-posted to 2 groups.

Show all headers | View raw

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

Back to comp.databases.ms-sqlserver | Previous | Next — Previous in thread | Find similar

Thread

SSE 2008: A Special Kind of Identity Gene Wirchenko <genew@ocis.net> - 2011-04-15 13:53 -0700
  Re: SSE 2008: A Special Kind of Identity Erland Sommarskog <esquel@sommarskog.se> - 2011-04-15 23:55 +0200

csiph-web