2

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.

marked as duplicate by ypercubeᵀᴹ sql-server Feb 6 at 15:55

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

  • @ypercubeᵀᴹ I have created a suggestion (36768604) on Microsoft's site. – Edmund Feb 6 at 15:07
  • 1
    I marked this as duplicate of similar questions. There are answers/workarounds there (one by Aaron Bertrand, using a trigger). If you feel your question is different, please explain and we could reopen it. – ypercubeᵀᴹ Feb 6 at 15:57
  • Neither options (trigger, new dynamically creatde sequence per ItemSet) looks pretty but there you go, you have options. – ypercubeᵀᴹ Feb 6 at 16:05