XPost: microsoft.public.sqlserver.programming   
   From: esquel@sommarskog.se   
      
   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   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)