SQL Server Identity column inserts

Question

I support a data replication product. I have a client who is very frustrated that SQL Server can't have a table with an Identity column that BOTH increments automatically when a row is added without providing a value for that column, and at the same time will accept/use a value when it is provided - and I might add, with both of those things happening continuously at a high rate and across hundreds of tables. They point to other databases that apparently can do this.

Everything I see online and my own experimentation seems to indicate that this simply can't be done in SQL Server, but I wanted to put it out there in case I'm just wrong and missing something. My only advice to them so far has been to switch to a Sequence (instead of Identity) and use it as a default value for the column. I've tested that and it works perfectly like they would want, but they are groaning at the idea of doing that for hundreds of tables. Thanks.

Answer 1

The point of an IDENTITY is that SQL Server is in control of it; you let SQL Server manage the value completely. What you really want is a SEQUENCE as a DEFAULT value.

CREATE TABLE dbo.SomeTable (ID int NOT NULL,
                            SomeColumn varchar(10));
GO
CREATE SEQUENCE dbo.SomeTableID START WITH 1 INCREMENT BY 1;
GO
ALTER TABLE dbo.SomeTable ADD CONSTRAINT PK_SomeTable PRIMARY KEY CLUSTERED (ID);
ALTER TABLE dbo.SomeTable ADD CONSTRAINT DF_SomeTableID DEFAULT NEXT VALUE FOR dbo.SomeTableID FOR ID;
GO
INSERT INTO dbo.SomeTable (SomeColumn)
VALUES ('abc'),('def');
GO
INSERT INTO dbo.SomeTable(ID,SomeColumn)
VALUES(3,'xyz');
GO
--Errors due to 3 already in use, but intended.
INSERT INTO dbo.SomeTable (SomeColumn)
VALUES ('abc');
GO
INSERT INTO dbo.SomeTable (SomeColumn)
VALUES ('def'); --4
GO

--Cleanup
DROP TABLE dbo.SomeTable;
DROP SEQUENCE dbo.SomeTableID;

db<>fiddle