insert then select scope_identity() generates duplicate rows

CREATE TABLE [schema].[table] (
  [column1] int IDENTITY NOT NULL,
  [column2] int NULL,
  [column3] int NULL,
  PRIMARY KEY CLUSTERED ([column1])
);
INSERT INTO schema.table (column2,column3) VALUES (1,1);
SELECT scope_identity();

it inserts TWO identical rows, and returns the primary key for t开发者_StackOverflow中文版he second inserted row.

It is probably a very basic reason, but google is not my friend on this one.

---

Please copy and paste verbatim

SET NOCOUNT ON;
USE tempdb;
CREATE TABLE dbo.[table] (
  [column1] int IDENTITY NOT NULL,
  [column2] int NULL,
  [column3] int NULL,
  PRIMARY KEY CLUSTERED ([column1])
);
INSERT INTO dbo.[table] (column2,column3) VALUES (1,1);
SELECT scope_identity();

SELECT * FROM dbo.[table]

You should get

---------------------------------------
1

column1     column2     column3
----------- ----------- -----------
1           1           1

When in doubt, always try on a clean new table in tempdb.

Other notes:

• If you are running insert from ASP.Net, check whether you have CSS elements (background image link) that is blank, it causes a 2nd request to the same page
• If you are running just a plain INSERT in SSMS or similar tool, check for triggers

To find triggers against a table using TSQL

select name, OBJECT_NAME(parent_object_id)
from sys.objects
where type='TR'
and OBJECT_NAME(parent_object_id) = 'table'  -- or whatever the table name is

To view the text of a trigger (or any module) using TSQL

select definition
from sys.sql_modules
where object_id = object_id('tg_table')  -- or whatever the trigger is named