0

I need to share a single SQL Server sequence between tables in two different SQL Server databases. This example works just fine for two existing tables and a sequence defined on the same database:

USE DatabaseA ;
GO

CREATE SEQUENCE dbo.TransactionSequence START WITH 1 INCREMENT BY 1 ;
GO

ALTER TABLE Table1 ADD TransactionSequenceID int NOT NULL 
    CONSTRAINT DF_Table1_TransactionSequence DEFAULT (NEXT VALUE FOR dbo.TransactionSequence) ;
ALTER TABLE Table2 ADD TransactionSequenceID int NOT NULL 
    CONSTRAINT DF_Table2_TransactionSequence DEFAULT (NEXT VALUE FOR dbo.TransactionSequence) ;
GO

However, this command fails for a table in a different database:

USE DatabaseB ;
GO

ALTER TABLE Table3 ADD TransactionSequenceID int NOT NULL 
    CONSTRAINT DF_Table3_TransactionSequence DEFAULT (NEXT VALUE FOR DatabaseA.dbo.TransactionSequence) ;
GO

It fails because SQL Server does not allow a DEFAULT constraint to reference a sequence in another database.
A sequence cannot be referenced indirectly through a synonym in this context, and NEXT VALUE FOR <sequence> is not allowed inside of a function or several other possibly-useful constructs. Is it possible for tables in different databases to share a single sequence? If so, then how?

kermit
  • 1
  • 1
  • 2
    *"Is it possible for tables in different databases to share a single sequence?"* Only by running the `INSERT` in the database the `SEQUENCE` exists in, and then using 3 part naming for the destination table. The fact that you want a shared `SEQUENCE` for 2 different databases denotes a design flaw in my opinion; clearly the objects should be in the *same* database, not different ones. – Thom A Jun 28 '22 at 14:24
  • @Larnu Thank you for the comment. Yes, this denotes a design flaw but these are legacy databases and unfortunately the tables cannot be moved. – kermit Jun 28 '22 at 14:43
  • Then they will need to have separate `SEQUENCE`s, @kermit . – Thom A Jun 28 '22 at 14:48
  • It can be done with an `INSTEAD OF INSERT` trigger on `Table3`, since that is free to call `NEXT VALUE FOR DatabaseA.dbo.TransactionSequence`, though it's not pretty -- the big drawback of `INSTEAD OF` triggers is that they break so easily if the schema changes (aside from the general pitfalls of all triggers, like making sure they don't nest inadvertently or generate output). – Jeroen Mostert Jun 28 '22 at 15:08
  • @JeroenMostert Your comment is probably as close as we will get to an Answer for the original technical question, although we all want to avoid triggers. Thank you for that. As an aside, it seems that based on Microsoft's restrictions on sequence use, perhaps sequences are intended to be database-scoped. Any attempt to violate that intent will result in a less-than-pretty workaround. – kermit Jun 29 '22 at 17:42

1 Answers1

1

I've implemented something like this in the past and depending on your need/use case it might work for you. Let's say that you have need for a sequence that generates WidgetID values in multiple databases. You'd do it something like this:

use [Widgets_001];
create sequence SQ_WidgetID
    as int
    start with 1
    minvalue 1
    maxvalue 1000000
    increment by 1;

use [Widgets_002];
create sequence SQ_WidgetID
    as int
    start with 1000001
    minvalue 1000001
    maxvalue 2000000
    increment by 1;

Once you have the sequences created, you're allowed to use them in whatever default constraints you want.

The idea here being that they allocate from disjoint ranges. Because the sequences allocate independently, the databases can even be on different servers. This does come with the cost of needing to monitor for range exhaustion (e.g. when the sequence in Widget_001 is over 950,000 it's probably time to start thinking about allocating a new range) and care in re-seeding the sequence to allocate from a new range (which isn't too bad if you have a way to look at all of the sequences across databases and always allocate new ranges to have minvalue strictly larger than the max of all of the existing maxvalues).

I'd personally resist the temptation to attribute any meaning to the values generated by these respective sequences. That is, it's easy to say "Well... that's WidgetID 1234. Therefore it's in Widgets_001.". That comes with its own problems.

Ben Thul
  • 31,080
  • 4
  • 45
  • 68
  • Thank you very much for the suggested solution. Unfortunately, my requirement is for the tables to share the same sequence. – kermit Jun 29 '22 at 00:52
  • Why? What is the business requirement that that technical requirement satisfies? – Ben Thul Jun 29 '22 at 03:36
  • We need to know the order in which records were inserted into the tables. – kermit Jun 29 '22 at 12:23
  • It is, in general, a bad idea to ascribe any meaning to these sorts of identifiers. They serve their purpose as giving you a means by which to uniquely identify a row in the database and, related, refer to it. Anything past that will eventually lead to irregularities. But even with the requirement of being able to order records, eventually someone will ask "*when* were they inserted?" or "what is the time difference between them?". Adding some sort of DateCreated column answers both (and still provides the ordering you're looking for). Live your life, but ⚠️. – Ben Thul Jun 29 '22 at 14:39
  • Thank you!!! Your comment made me reconsider using a DateCreated column. Given the volume of transactions, datetime would not work, which is what I thought of first. Only now did I think of using a datetime2 data type column with sub-microsecond precision to order the records. I can't mark your answer as a resolution of the stated technical issue, but your comment was very helpful!!! – kermit Jun 29 '22 at 17:05
  • Eh... don't worry about the upvote - I'm not in it for the fake internet points. I'm just happy to provide value where I can. Cheers! :) – Ben Thul Jun 29 '22 at 18:47