CODEWITHSUNDEEP
sql-serversequenceidentity
CR Anandan
CR Anandan

Reputation: 9

Convert an existing Identity column to sequence

Existing table:

CREATE TABLE [dbo].[Airlines_Mast]
(
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Airlines_ID]  AS ('AS'+right('00000'+CONVERT([varchar],[ID]),(6))) PERSISTED NOT NULL,
    [Airlines_Name] [nvarchar](100) NOT NULL
)

Created sequence:

CREATE SEQUENCE [Airlines_Mast_SEQ]
AS INTEGER
START WITH 3
INCREMENT BY 1
MINVALUE 1
MAXVALUE 99999;

Existing identity column:

[ID] [int] IDENTITY(1,1) NOT NULL

[ID] [INT] DEFAULT NEXT VALUE FOR [Airlines_Mast_SEQ] PRIMARY KEY

How to convert an existing identity column to using a sequence?

Upvotes: 1

Views: 1494

Answers (1)

Dan Guzman
Dan Guzman

Reputation: 46223

If your original table has a primary key constraint like the new one, you can use SWITCH to move data to a new table with the same schema (including indexes). The IDENTITY property meta-data will not be retained. You just need to ensure the sequence START WTIH values is higher than the existing MAX(ID) value

For Example:

CREATE SEQUENCE [Airlines_Mast_SEQ]
    AS INTEGER
    START WITH 3
    INCREMENT BY 1
    MINVALUE 1
    MAXVALUE 99999; 
CREATE TABLE dbo.Airlines_Mast
(
    [ID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
    [Airlines_ID]  AS ('AS'+right('00000'+CONVERT([varchar],[ID]),(6))) PERSISTED NOT NULL,
    [Airlines_Name] [nvarchar](100) NOT NULL
);
INSERT INTO dbo.Airlines_Mast (Airlines_Name) VALUES (N'example1');
INSERT INTO dbo.Airlines_Mast (Airlines_Name) VALUES (N'example2');
SELECT * FROM dbo.Airlines_Mast;
GO
SET XACT_ABORT ON;
BEGIN TRY;
    BEGIN TRAN;
    CREATE TABLE [dbo].[Airlines_Mast_Sequence]
    (
        [ID] [int] NOT NULL DEFAULT NEXT VALUE FOR [Airlines_Mast_SEQ] PRIMARY KEY,
        [Airlines_ID]  AS ('AS'+right('00000'+CONVERT([varchar],[ID]),(6))) PERSISTED NOT NULL,
        [Airlines_Name] [nvarchar](100) NOT NULL
    );
    ALTER TABLE dbo.Airlines_Mast
        SWITCH TO dbo.Airlines_Mast_Sequence;
    DROP TABLE dbo.Airlines_Mast;
    EXEC sp_rename N'dbo.Airlines_Mast_Sequence',N'Airlines_Mast';
    COMMIT;
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0 ROLLBACK;
    THROW;
END CATCH;
GO

INSERT INTO dbo.Airlines_Mast (Airlines_Name) VALUES (N'example3');
SELECT * FROM dbo.Airlines_Mast;

Results:

+----+-------------+---------------+
| ID | Airlines_ID | Airlines_Name |
+----+-------------+---------------+
|  1 | AS000001    | example1      |
|  2 | AS000002    | example2      |
+----+-------------+---------------+

+----+-------------+---------------+
| ID | Airlines_ID | Airlines_Name |
+----+-------------+---------------+
|  1 | AS000001    | example1      |
|  2 | AS000002    | example2      |
|  3 | AS000003    | example3      |
+----+-------------+---------------+

Upvotes: 2

Related Questions