0

I need to create counter in database for document number. I.e. DOC_NUM_1, DOC_NUM_2 these are document numbers, so i need to store and increment counter. What the best way to acchive this? Database - sqlserver. And there are many concurrent users.

AstroCB
  • 12,337
  • 20
  • 57
  • 73
IComparable
  • 71
  • 1
  • 1
  • 10
  • 1
    This is what an *identity column* is for – Alex K. Nov 06 '14 at 16:07
  • Use `Identity` type for your column and have your string representation either in a separate column or concatenate in your C# code. – Habib Nov 06 '14 at 16:07
  • Related if not duplicate: http://stackoverflow.com/questions/8170950/how-do-i-add-string-with-auto-increment-value-in-sql-server – Habib Nov 06 '14 at 16:07
  • Yep. Use an identity column. The only way you can pull this off otherwise is to apply a row or perhaps even a table lock in the database each time, you need to update or retrieve the counter, which is a seriously bad thing to do. Unless of course, you have masochistic tendencies and enjoy dealing with database deadlocks. – Chris Pratt Nov 06 '14 at 16:16
  • I agree that Identity is the correct approach, but you should be aware that it is possible to have gaps in the sequences, which may or may not be acceptable for your solution. – David Martin Nov 06 '14 at 17:00
  • And if I want to prevent gaps, i need do it with blocking or in transaction with serialize isolation level. Can I do it using EF? – IComparable Nov 07 '14 at 16:14
  • Gaps can appear if some transactions are rolled back or if some rows are deleted.While you can prevent gaps caused by rollbacks by making your transactions to run sequentially (not simultaneously), you can't prevent gaps caused by deleted rows. At least, I don't know how to do it, unless you forbid deleting rows. – Vladimir Baranov Feb 17 '15 at 05:22

2 Answers2

2

You didn't say what version of SQL Server you are using. If it's 2012 or higher, you could use a Sequence. You can learn more about them here. Sequences have advantages over using an Identity column. If you're using a version prior to 2012, you will probably need to use an Identity column. Or roll your own solution.

Randy Minder
  • 47,200
  • 49
  • 204
  • 358
  • Or program a sequence. People ahve been doing that for years - it is quite trivial. Some SP / functions and you are done. – TomTom Nov 06 '14 at 16:08
0

Consider using IDENTITY column.

Max Brodin
  • 3,903
  • 1
  • 14
  • 23