Second Identity Column Workaround: Computed Column or Sequence

A table can have only one identity column, so a second identity column workaround has to come from somewhere else. A computed column and a sequence both count down from 9999, and each one fails in its own way.

One Identity per Table

The last section of a health check is questions and answers. One DBA asked whether a table can have two identity columns. The answer is yes and no. A table can have only one column with the IDENTITY property. A second identity column workaround needs a different tool.

The client wanted two counters in one table. One would start at 1 and grow by 1. The other would start at 9999 and shrink by 1. The demo uses a database named SecondIdentityDemo. First, the direct attempt.

IF DB_ID(N'SecondIdentityDemo') IS NULL CREATE DATABASE SecondIdentityDemo;
GO
USE SecondIdentityDemo;

DROP TABLE IF EXISTS dbo.TwoIdentity;
CREATE TABLE dbo.TwoIdentity (ID int IDENTITY(1,1) NOT NULL, SecondID int IDENTITY(9999,-1) NOT NULL);

Msg 2744, Level 16, State 2, Line 2
Multiple identity columns specified for table 'TwoIdentity'. Only one identity column per table is allowed.

Workaround One: a Computed Column

A computed column can follow the identity column. The formula 10000 minus ID gives 9999 for the first row, 9998 for the second, and so on. The value is calculated, so it needs no storage and no second counter.

DROP TABLE IF EXISTS dbo.Countdown;
CREATE TABLE dbo.Countdown (
    ID int IDENTITY(1,1) NOT NULL CONSTRAINT PK_Countdown PRIMARY KEY,
    SecondID AS 10000 - ID,
    Note varchar(40) NOT NULL
);
INSERT INTO dbo.Countdown (Note) VALUES ('oolong'), ('assam'), ('nilgiri'), ('darjeeling'), ('matcha');
SELECT ID, SecondID, Note FROM dbo.Countdown ORDER BY ID;

SecondID counts down as asked. It is not a second identity, though. It is a formula over the first one, so it has no counter of its own. Two things follow from that. The formula never stops at 1. Insert an ID of 10001 on purpose, and the second value turns negative with no error.

SET IDENTITY_INSERT dbo.Countdown ON;
INSERT INTO dbo.Countdown (ID, Note) VALUES (10001, 'late arrival');
SET IDENTITY_INSERT dbo.Countdown OFF;
SELECT ID, SecondID, Note FROM dbo.Countdown WHERE ID > 5;

The second thing is that nobody can write to the column. A computed column is read only, and an UPDATE fails.

UPDATE dbo.Countdown SET SecondID = 5 WHERE ID = 1;

Msg 271, Level 16, State 1, Line 1
The column "SecondID" cannot be modified because it is either a computed column or is the result of a UNION operator.

Read only does not mean slow to search. An index on the computed column works, because the formula is deterministic and uses only whole numbers. On a large table a lookup by the second value can use the index. On the six rows of this demo the optimizer scans the whole table. The row for 9995 is the fifth one.

CREATE INDEX IX_Countdown_SecondID ON dbo.Countdown (SecondID);
SELECT ID, SecondID, Note FROM dbo.Countdown WHERE SecondID = 9995;

Workaround Two: a Sequence

A sequence is a counter that lives outside the table. It can start anywhere, step by any amount and stop at a limit. A default constraint pulls the next value for each new row. The column is an ordinary int, so it is writable, and it counts on its own. A default does not stop duplicates. Add a unique constraint on SecondID if it must never repeat.

DROP SEQUENCE IF EXISTS dbo.CountdownSeq;
CREATE SEQUENCE dbo.CountdownSeq AS int START WITH 9999 INCREMENT BY -1 MINVALUE 1 MAXVALUE 9999 NO CYCLE;
DROP TABLE IF EXISTS dbo.Tickets;
CREATE TABLE dbo.Tickets (
    ID int IDENTITY(1,1) NOT NULL CONSTRAINT PK_Tickets PRIMARY KEY,
    SecondID int NOT NULL CONSTRAINT DF_Tickets_SecondID DEFAULT (NEXT VALUE FOR dbo.CountdownSeq),
    Note varchar(40) NOT NULL
);
INSERT INTO dbo.Tickets (Note) VALUES ('first'), ('second');
BEGIN TRANSACTION;
INSERT INTO dbo.Tickets (Note) VALUES ('rolled back');
ROLLBACK TRANSACTION;
INSERT INTO dbo.Tickets (Note) VALUES ('after rollback');
SELECT ID, SecondID, Note FROM dbo.Tickets ORDER BY ID;

The insert that rolled back is gone, and it took one value from each counter. The next row got ID 4 and SecondID 9996. Identity values and sequence values are not returned after a rollback. A server that stops without a clean shutdown can lose more, because both keep a cache of values in memory.

The sequence has a stop. A sequence with two values and no cycle returns 2, then 1, and then raises an error. That is a safer failure than a silent negative number.

DROP SEQUENCE IF EXISTS dbo.TinySeq;
CREATE SEQUENCE dbo.TinySeq AS int START WITH 2 INCREMENT BY -1 MINVALUE 1 MAXVALUE 2 NO CYCLE;
SELECT NEXT VALUE FOR dbo.TinySeq AS V1;
SELECT NEXT VALUE FOR dbo.TinySeq AS V2;
SELECT NEXT VALUE FOR dbo.TinySeq AS V3;

Msg 11728, Level 16, State 1, Line 5
The sequence object 'TinySeq' has reached its minimum or maximum value. Restart the sequence object to allow new values to be generated.

Which Workaround to Choose

Choose the computed column when the second value only mirrors the first. Use it when you never need to change that value. It costs nothing to store. Choose the sequence when the second counter must stand on its own, or when it must stop at a limit. Either second identity column workaround leaves gaps after a rollback, so neither suits a number that must have no holes.

Ask why you need two counters before you pick. A number that must have no gaps cannot come from an identity or a sequence. Keep a counter row and update it inside the same transaction. A key that only has to grow does not care about gaps. For a real identity that counts down, read Negative Identity Seed and Negative Increment in SQL Server.

Do You Need Two Counters at All?

You could argue that two counters in one table point to a design problem. A second value that is the first one turned upside down adds nothing you could not compute in a query. That is true when the reverse number is only for display. Ask who reads the second number and what they do with it. The sequence earns its place when the second number has its own meaning. A ticket number that runs on its own is one.

What to Remember

A table has one identity column. For a second identity column workaround, use a computed column when it only mirrors the first. Use a sequence when it must count on its own. Expect gaps from both.

When you finish the demo, remove the database.

USE master;
GO
IF DB_ID(N'SecondIdentityDemo') IS NOT NULL
BEGIN
    ALTER DATABASE SecondIdentityDemo SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE SecondIdentityDemo;
END;

A second identity column is not a second feature, it is a formula or a sequence you choose to write.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.