Specify "NEXT VALUE" for INSERT statement using identity column in SQL Server

Question

Consider the following table and SQL from Microsoft's INSERT documentation that deals with IDENTITY columns:

CREATE TABLE dbo.T1 (column_1 int IDENTITY, column_2 VARCHAR(30));
GO

INSERT T1 (column_2) VALUES ('Row #2');

The INSERT statement does not specify column_1 as a column of the table, and SQL Server auto-populates the next value for that identity column. This is the normal way identity columns are handled.

How can I have the same behavior, while also specifying the column name?

For example, I'm looking for something like:

INSERT INTO T1 (column_1, column_2) 
VALUES (NEXT VALUE, 'Row #3');
GO

I don't believe NEXT VALUE works here, but is there something that does work? Is there a key token or function that will indicate that the identity column should be used?

Note: the reason I ask is that the framework I'm using requires all columns to be specified in the column list.

Answer 1

If you are on SQL Server 2012 and later, you can use sequence. But you must remove the IDENTITY property from Column1 first. This can only be done by copy-and-rename a new table.

CREATE SEQUENCE Column1_Sequence
    AS int
    START WITH 0;

CREATE TABLE T1
(
    Column1     int DEFAULT (NEXT VALUE FOR Column1_Sequence) PRIMARY KEY
,   Column2     nvarchar(30)
)

After that, you can insert data into the table in 2 ways:

INSERT INTO T1 (Column1, Column2)
    SELECT      NEXT VALUE FOR Column1_Sequence
            ,   'Row #2'

INSERT INTO T1 (Column2)
    SELECT      'Hello world'