SQL Server based Counter

Question

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.

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: stackoverflow.com/questions/661998/… ) — Najzero
– Najzero, Commented Sep 11, 2012 at 10:04

Richard · Accepted Answer · 2012-09-11 10:06:38Z

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.

answered Sep 11, 2012 at 10:06

Richard

30.7k12 gold badges79 silver badges121 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL Server based Counter

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related