0

Possible Duplicate:
Read and Increment int value in SQL Server

I am currently working on "Double Entry Accounting" in relational database.

According to the article, it quotes

"The primary key or part of the primary key of the 'POSTING' table should be a system generated sequence number. It should also be generated in such a way that no gaps can appear in the sequence (through a transaction rolling back, for example). This is part of ensuring that no entries are ever deleted."

I am trying to find a way to achieve this goal. I cannot use the identity column because the transaction rollback, so my 'Posting' table has a column called seqNum which going to be used for this but how can I ensure the values in this column are in sequence. We have a lots of users that can be using our website at the same time.

Do I have to use table lock to ensure a sequence access to this table until the transaction is committed? But how?

I am currently using Microsoft Sql Server 2008.

Community
  • 1
  • 1
PYSW
  • 1
  • SQL Server 2008 doesn't have `Sequences` - that's a new feature in SQL Server **2012**. In SQL Server 2008, you'll have to use the [`IDENTITY` attribute](http://msdn.microsoft.com/en-us/library/ms186775.aspx) on a (numeric) column in your table. – marc_s Jul 31 '12 at 10:18
  • The "Read and Increment into value in SQL Server" would only ensure there is no gap within the batch insert but will it ensure there is no gaps between two transactions when one is rollback? – PYSW Jul 31 '12 at 22:54
  • @PYSW - Yes. You need to call the procedure `dbo.GetSequence` within the same transaction as the inserts to ensure this. – Martin Smith Aug 01 '12 at 07:38

0 Answers0