For example, there is a table

 int type
 int number
 int value

How to make that when inserting a value into a table indexing started from 1 for different types.

type 1 => number 1,2,3...
type 2 => number 1,2,3...

That is, it will look like this.

Special thanks to @Larnu.

As a result, in my case, the best solution would be to create a table for each type.

CodePudding user response：

As I mentioned in the comments, neither IDENTITY nor SEQUENCE support the use of another column to denote what "identity set" they should use. You can have multiple SEQUENCEs which you could use for a single table, however, this doesn't scale. If you are specific limited to 2 or 3 types, for example, you might choose to create 3 SEQUENCE objects, and then use a stored procedure to handle your INSERT statements. Then, when a user/application wants to INSERT data, they call the procedure and that procedure has logic to use the SEQUENCE based on the value of the parameter for the type column.

As mentioned, however, this doesn't scale well. If you have an undeterminate number of values of type then you can't easily handle getting the right SEQUENCE and handling new values for type would be difficult too. In this case, you would be better off using a IDENTITY and then a VIEW. The VIEW will use ROW_NUMBER to create your identifier, while IDENTITY gives you your always incrementing value.

CREATE TABLE dbo.YourTable (id int IDENTITY(1,1),
                            [type] int NOT NULL,
                            number int NULL,
                            [value] int NOT NULL);
GO

CREATE VIEW dbo.YourTableView AS
    SELECT ROW_NUMBER() OVER (PARTITION BY [type] ORDER BY id ASC) AS Identifier,
           [type],
           number,
           [value]
    FROM dbo.YourTable;

Then, instead, you query the VIEW, not the TABLE.

If you need consistency of the column (I name identifier) you'll need to also ensure row(s) can't be DELETEd from the table. Most likely by adding an IsDeleted column to the table defined as a bit (with 0 for no deleted, and 1 for deleted), and then you can filter to those rows in the VIEW:

CREATE VIEW dbo.YourTableView AS
    WITH CTE AS(
        SELECT id,
               ROW_NUMBER() OVER (PARTITION BY [type] ORDER BY id ASC) AS Identifier,
               [type],
               number,
               [value],
               IsDeleted
        FROM dbo.YourTable)
    SELECT id,
           Identifier,
           [type],
           number,
           [value]
    FROM CTE
    WHERE IsDeleted = 0;

You could, if you wanted, even handle the DELETEs on the VIEW (the INSERT and UPDATEs would be handled implicitly, as it's an updatable VIEW):

CREATE TRIGGER trg_YourTableView_Delete ON dbo.YourTableView 
INSTEAD OF DELETE AS
BEGIN
    SET NOCOUNT ON;
    UPDATE YT
    SET IsDeleted = 1
    FROM dbo.YourTable YT
         JOIN deleted d ON d.id = YT.id;
END;
GO

db<>fiddle

For completion, if you wanted to use different SEQUENCE object, it would look like this. Notice that this does not scale easily. I have to CREATE a SEQUENCE for every value of Type. As such, for a small, and known, range of values this would be a solution, but if you are going to end up with more value for type or already have a large range, this ends up not being feasible pretty quickly:

CREATE TABLE dbo.YourTable (identifier int NOT NULL,
                            [type] int NOT NULL,
                            number int NULL,
                            [value] int NOT NULL);

CREATE SEQUENCE dbo.YourTable_Type1
    START WITH 1 INCREMENT BY 1;

CREATE SEQUENCE dbo.YourTable_Type2
    START WITH 1 INCREMENT BY 1;

CREATE SEQUENCE dbo.YourTable_Type3
    START WITH 1 INCREMENT BY 1;

GO

CREATE PROC dbo.Insert_YourTable @Type int, @Number int = NULL, @Value int AS
BEGIN

    DECLARE @Identifier int;
    IF @Type = 1
        SELECT @Identifier = NEXT VALUE FOR dbo.YourTable_Type1;
    IF @Type = 2
        SELECT @Identifier = NEXT VALUE FOR dbo.YourTable_Type2;
    IF @Type = 3
        SELECT @Identifier = NEXT VALUE FOR dbo.YourTable_Type3;

    INSERT INTO dbo.YourTable (identifier,[type],number,[value])
    VALUES(@Identifier, @Type, @Number, @Value);

END;