1

I'm looking for a way to implement a globally available 'counter' variable, similar to a SEQUENCE but something that I can easily increment and decrement using transact SQL.

I could create a table and insert a counter row but I'm looking for something very fast and easy to manage.

Tanner
  • 22,205
  • 9
  • 65
  • 83
checkmate711
  • 3,301
  • 2
  • 35
  • 45
  • what's wrong with sequence? – Dmitrij Kultasev Sep 25 '17 at 13:39
  • 3
    1) Create a table with one row. 2) Use `UPDATE` with the `SERIALIZABLE` hint to increment or decrement it. 3) Enjoy your global concurrency hotspot. – Jeroen Mostert Sep 25 '17 at 13:41
  • @Dmitrij: I would need to be able to increment AND decrement. – checkmate711 Sep 25 '17 at 13:42
  • 1
    perhaps if you explain the actual problem that this is intended to solve, you'll get a better answer. – Tanner Sep 25 '17 at 13:45
  • You don't say what version of SQL Server.....In-Memory Tables might be fast enough? – SQLBadPanda Sep 25 '17 at 13:46
  • @dutchman711 it's a hack, but you can restart sequence at any value (emulating decrement). But I do not like this approach – Dmitrij Kultasev Sep 25 '17 at 13:51
  • @Nick Fry: SQL Server 2012. Thanks - I'll look into in-memory tables. – checkmate711 Sep 25 '17 at 13:58
  • @Dmitrij: That's a good idea and may solve the purpose. Thanks! – checkmate711 Sep 25 '17 at 13:59
  • Tip: An `update` statement can update both columns and variables, e.g. `update SerialTable set @Before = Serial, Serial +=1, @After = Serial;`. – HABO Sep 25 '17 at 14:07
  • SQL Server 2012 has no in-memory OLTP, that starts from SQL Server 2014. Note that, due to the overhead of transaction logging, *any* solution you come up with for counters in SQL Server itself will be sub-optimal at best and a performance drag at worst. If at all possible, try restructuring your solution so that, at the very least, the counter can be partitioned in some way so as to not form a locking hotspot for every single session. Alternatively, if the counter need not persist, consider a CLR procedure that uses `Interlocked.Increment/Decrement` (this may require an `UNSAFE` assembly). – Jeroen Mostert Sep 25 '17 at 14:09
  • Try Exteneded Properties on the database object https://technet.microsoft.com/en-us/library/ms190243%28v=sql.105%29.aspx?f=255&MSPPError=-2147217396 – cloudsafe Sep 26 '17 at 09:01
  • [There is no way to declare a global variable in Transact-SQL.](https://stackoverflow.com/a/22372569/4519059) ;). – shA.t Nov 25 '17 at 11:39

1 Answers1

0

I'm uncertain as to the problem you are trying to solve (as Tanner mentioned). However, were I trying to do this on SQL, to avoid blocking issues and to allow the increment/decrement to function without interfering with one another, I'd probably use a SQL Service Broker queue.

If you reference my LinkedIn article on SSB queue performance, it's pretty good. I got over a million throughput per hour on my laptop with a single thread. You could easily push an XML object through with either a 1 or -1 and have the downstream activation procedure perform an "add" to a counter. You can then make that run as fast as you want to scale it.

This will NOT be any faster at low speeds than using a global temp table or dedicated table directly, but once you start moving up in speed you can scale the solution without blocking issues.

Katherine Elizabeth Lightsey
  • 853
  • 5
  • 12