Is it possible to create a windowed identity column that automatically initializes and increments in SQL Server 2017 or later?
I would like the conceptual functionality of SelectedSubsetId below.
CREATE TABLE dbo.SelectedSubset
(
ItemSetId INT NOT NULL FOREIGN KEY REFERENCES dbo.ItemSet(ItemSetId),
SelectedSubsetId INT NOT NULL IDENTITY(1,1) OVER(PARTITION BY ItemSetId),
PRIMARY KEY CLUSTERED (ItemSetId, SelectedSubsetId )
)
Where SelectedSubsetId would start at 1 and increment by 1 for each ItemSetId entered. Another table would would foreign key this primary key and add the item ids of the set
CREATE TABLE dbo.SubsetItems
(
ItemSetId INT NOT NULL,
SelectedSubsetId INT NOT NULL,
ItemId INT NOT NULL,
FOREIGN KEY (ItemSetId, SelectedSubsetId) REFERENCES dbo.SelectedSubset(ItemSetId, SelectedSubsetId),
FOREIGN KEY (ItemSetId, ItemId) REFERENCES dbo.ItemSetItems(ItemSetId, ItemId),
PRIMARY KEY CLUSTERED (ItemSetId, SelectedSubsetId, ItemId)
)
In this concept SelectedSubsetId is only defined by its ItemIds. It has no attributes of its own that warrant creating a table to hold additional information.
