SQL Server Provider: Consider using OUTPUT statement within INSERT INTO instead of SCOPE_IDENTITY

[isolaol]

Dear developers,

IDENTITY(n,n) is becoming aged with Microsoft SQL Server 2012. The new server version has Oracle like sequence facility which offers advantages over IDENTITY columns. For example, we can insert values to a column without "SET IDENTITY_INSERT table ON" statement (which requires ALTER permissions).

Unluckily, SCOPE_IDENTITY() may stop working with sequences. I recommend you to consider switching from SCOPE_IDENTITY() to OUTPUT statement which should work with sequence columns, too.

To illustrate the change needed in SQL code generation I have attached few sample statements below.

-- create a sample table, with IDENTITY primary key
CREATE TABLE dbo.TableWithIdentity (
     KeyCol int IDENTITY(1,1) NOT NULL PRIMARY KEY,
     ValCol nvarchar(50) NULL
);

-- insert a row, the old method
INSERT INTO dbo.TableWithIdentity(ValCol) VALUES (N'x'); SELECT SCOPE_IDENTITY() AS ID;

-- insert a row, the new method with OUTPUT statement
INSERT INTO dbo.TableWithIdentity(ValCol) OUTPUT INSERTED.KeyCol AS ID VALUES (N'y');

-- show contents of sample table
SELECT * FROM dbo.TableWithIdentity;

-- remove sample table
DROP TABLE dbo.TableWithIdentity;

links to Microsoft SQL Server 2008 documentation of INSERT INTO and OUTPUT statements: http://technet.microsoft.com/en-us/library/ms174335(v=sql.100).aspx http://technet.microsoft.com/en-us/library/ms177564(v=sql.100).aspx

-- create a sample sequence of numbers
CREATE SEQUENCE dbo.SampleSeq AS int START WITH 1 INCREMENT BY 1;

-- create a sample table, with sequence as default value in primary key column
CREATE TABLE dbo.TableWithSequence (
     KeyCol int NOT NULL PRIMARY KEY DEFAULT (NEXT VALUE FOR dbo.SampleSequence),
     ValCol nvarchar(50) NULL
);

-- insert a row, the old method, WILL RETURN NULL, NO GOOD !!!
INSERT INTO dbo.TableWithSequence(ValCol) VALUES (N'x'); SELECT SCOPE_IDENTITY() AS ID;

-- insert a row, the new method with OUTPUT should work with sequence default
INSERT INTO dbo.TableWithSequence(ValCol) OUTPUT INSERTED.KeyCol AS ID VALUES (N'y');

-- show contents of sample table
SELECT * FROM dbo.TableWithSequence;

-- remove sample table
DROP TABLE dbo.TableWithSequence;

link to Microsoft SQL Server 2012 documentation of sequence numbers: http://technet.microsoft.com/en-us/library/ff878058.aspx

Fortunately, the OUTPUT construct works at least from SQL Server 2005 and onwards.

I hope this explanation helps you build migration path to the new SQL Server version.

Kind regards, Olli-Pekka Isola

[sapiens]

Thanks, good to know

[isolaol]

Hi sapiens,

For reference purposes, the page https://github.com/toptensoftware/PetaPoco/blob/v5/PetaPoco/DatabaseTypes/SqlServerDatabaseType.cs contains database specific implementation of OUTPUT clause. Take a look at GetInsertOutputClause method.

The page https://github.com/toptensoftware/PetaPoco/blob/v5/PetaPoco/Database.cs shows how Insert method generates SQL statement with OUTPUT clause. Scroll down to lines 1140-1152.

These two references are from PetaPoco v5 branch.

Rgds, Olli-Pekka

[sapiens]

This will be included in the next major version

[isolaol]

Excellent! You are moving ahead of Dapper.Extensions and co. :-)

As you may notice, OUTPUT clause is powerful method of reading back any data type (not just integers) from any column set by database engine. The OUTPUT clause comes handy with identity columns, default values like sequences or with computed columns. Might even help with columns that triggers update (haven't tested though).

[sapiens]

done