1

Whats the best way to implement a unique counter generator in SQL Server 2005? So I have a single field in a table with a single value which is the counter.

I want to get the current value and also increment by 1.

So database will hold the next incremented value. I have tried several select + update combos but since this counter is being hit very hard (probably 5000 times per minute) from various calls from independent applications, the application seems to be slow. How can I get a better counter? I need the value to be serial so we cannot have a different table for each application.

I have suggestion from someone here: http://www.sqlines.com/mysql/how-to/select-update-single-statement-race-condition but not sure it is right or may have other issues.

psur
  • 4,400
  • 26
  • 36
Raj Chandrachud
  • 11
  • 1
  • I just deleted my answer, SEQUENCES like in oracle just hit SQL-server 2012, since you use 2005 you will have to use your own logic (something like: http://stackoverflow.com/questions/661998/oracle-sequence-but-then-in-ms-sql-server ) – Najzero Sep 11 '12 at 10:04

1 Answers1

0

Can you not just Insert a row into a table using an Identity column? Once you retrieve the inserted counter value using SCOPE_IDENTITY, you'll know what the previous version was. It'll be fast because it's not checking existing rows. If you don't need to store per-request rows, you could clear these out occasionally.

Richard
  • 29,854
  • 11
  • 77
  • 120