Not because of the users question itself, I thought it was a terrible premise all around to assume that an index violation would lead to the population of an identity value. No, what I found fascinating was my dive into
- Unique index with IGNORE_DUP_KEY set
- OUTPUT clause
- MERGE statement
- INSTEAD OF triggers
Highlights of my findings
- MERGE statement with OUTPUT clause seemed to offer the best fit for what the questioner wanted
- OUTPUT will not have access to the inserted logical table even though the insert will have consumed an identity value and rolled back
- You can fake it through an INSTEAD OF trigger but it's an ugly hack and you wouldn't able to take advantage of the OUTPUT clause
- The inserted virtual table within an INSTEAD OF trigger has identity values populated with 0
- You cannot update the inserted/deleted logical tables
- The OUTPUT clause on a table with an instead of trigger operates on the virtual tables
-- This script demonstrates the usage of -- IGNORE_DUP_KEY option for an index -- http://msdn.microsoft.com/en-us/library/ms186869.aspx -- Why you'd want this behaviour is left as -- an excercise to the reader -- SET NOCOUNT ON IF EXISTS(SELECT 1 FROM sys.tables T WHERE T.name = 'DupesOk' AND T.schema_id = schema_id('dbo')) BEGIN DROP TABLE dbo.DupesOk END GO CREATE TABLE dbo.DupesOk ( dupe_id int identity(1,1) NOT NULL PRIMARY KEY , name varchar(50) NOT NULL ) -- Create an index that is unique but -- violation of the unique constraint is -- merely discarded with warning instead of -- blowing up CREATE UNIQUE INDEX uq_dupes_name ON dbo.DupesOk ( name ) WITH IGNORE_DUP_KEY -- Add a name and emit the identity value -- from the inserted virtual table INSERT INTO dbo.DupesOk OUTPUT inserted.dupe_id , inserted.name SELECT 'Peter Parker' -- Old-school means of showing the identity -- values, 1's across the board -- See earlier posting -- http://billfellows.blogspot.com/2009/10/scope-and-identity.html SELECT @@IDENTITY AS six_of_one , SCOPE_IDENTITY() half_dozen_of_other , IDENT_CURRENT('dbo.DupesOk') AS current_identity_value -- Add a new name and emit the identity value -- from the inserted virtual table INSERT INTO dbo.DupesOk OUTPUT inserted.dupe_id , inserted.name SELECT 'Spider man' -- Same as above, 2s across the board SELECT @@IDENTITY AS six_of_one , SCOPE_IDENTITY() half_dozen_of_other , IDENT_CURRENT('dbo.DupesOk') AS current_identity_value -- Insert a duplicate value for the unique index -- watch it not explode with a output message of -- 'Duplicate key was ignored.' INSERT INTO dbo.DupesOk OUTPUT -- This won't show anything as there is nothing to show inserted.dupe_id , inserted.name SELECT 'Peter Parker' -- The first two remain 2's as they belong to the successful -- insert of Spider man. ident_current shows that the value was -- incremented. The calling code did not do a lookup to SELECT @@IDENTITY AS this_identity_belongs_to_spider_man , SCOPE_IDENTITY() this_identity_also_belongs_to_spider_man -- As expected, the value is now 3, it got incremented , IDENT_CURRENT('dbo.DupesOk') AS current_identity_value ; MERGE -- target table dbo.DupesOk AS T USING ( -- source system SELECT 'Hal Jordan' AS name ) AS S ON S.name = T.name WHEN MATCHED THEN UPDATE SET T.name = S.name WHEN NOT MATCHED THEN INSERT ( [name] ) VALUES ( [name] ) -- 4 | Hal Jordan | INSERT OUTPUT inserted.dupe_id , inserted.name , $action ; -- 4's as expected SELECT @@IDENTITY AS hal_jordan , SCOPE_IDENTITY() still_hal_jordan , IDENT_CURRENT('dbo.DupesOk') AS current_identity_value -- Add someone else just to get the ids to flip INSERT INTO dbo.DupesOk OUTPUT inserted.dupe_id , inserted.name SELECT 'Tony Stark' -- 5's SELECT @@IDENTITY AS tony_stark , SCOPE_IDENTITY() still_tony_stark , IDENT_CURRENT('dbo.DupesOk') AS current_identity_value ; -- Try inserting an existing id MERGE -- target table dbo.DupesOk AS T USING ( -- source system SELECT 'Hal Jordan' AS name ) AS S ON S.name = T.name WHEN MATCHED THEN UPDATE SET T.name = S.name WHEN NOT MATCHED THEN INSERT ( [name] ) VALUES ( [name] ) -- 4 | Hal Jordan | UPDATE OUTPUT inserted.dupe_id , inserted.name , $action ; -- Still 5's SELECT @@IDENTITY AS tony_stark , SCOPE_IDENTITY() still_tony_stark , IDENT_CURRENT('dbo.DupesOk') AS current_identity_value GO -- What if we try a trigger? -- It would need to be an instead of trigger -- as the value will have already been -- http://msdn.microsoft.com/en-us/library/ms189799.aspx -- http://msdn.microsoft.com/en-us/library/ms175089.aspx CREATE TRIGGER tr_dupes_insert ON dbo.DupesOk INSTEAD OF INSERT AS BEGIN SET NOCOUNT ON -- variety of different approaches here but -- I'll attempt the insert and if no rows -- are affected, then we know it's an existing -- row and lookup the identity DECLARE @ident TABLE ( dupe_id int NOT NULL , name varchar(50) NOT NULL ) -- Only n00bs code triggers for single rows INSERT INTO dbo.DupesOk ( name ) -- output clause -- http://msdn.microsoft.com/en-us/library/ms177564.aspx OUTPUT -- the output's virtual table -- recursion is deep, yo inserted.dupe_id , inserted.name INTO @ident SELECT I.name FROM -- the trigger's virtual table -- fascinatingly enough, the value for -- an identity field pre-insert on an -- instead of trigger is 0 and not NULL -- as one would assume inserted I -- Now we need to add anyone into the -- table variable that didn't get inserted -- into @ident in the previous statement INSERT INTO @ident SELECT D.dupe_id , D.name FROM inserted I INNER JOIN dbo.DupesOk D ON D.name = I.name LEFT OUTER JOIN @ident tv -- can't match on ids here -- as they all come in as zero ON tv.name = I.name WHERE tv.dupe_id IS NULL SELECT I.dupe_id , I.name FROM @ident I -- To make OUTPUT work correctly, we'd need to -- "fix" the values in the inserted virtual tables -- but uncommenting this will result in a -- trigger creation error of -- "The logical tables INSERTED and DELETED cannot be updated" --UPDATE -- I --SET -- dupe_id = -1 --FROM -- inserted i -- INNER JOIN -- @ident TV -- ON TV.name = i.name END GO DECLARE @idents TABLE ( dupe_id int , name varchar(50) ) -- We should see -- 1 | Peter Parker -- 7 | Barry Allen -- -- 6 was consumed by the double pump of Hal Jordan -- results were surprising, to me at least INSERT INTO dbo.DupesOk -- this will generate an error -- The target table 'dbo.DupesOk' of the DML statement cannot have any enabled triggers if the statement contains an OUTPUT clause without INTO clause. -- unless we dump output results into a table OUTPUT inserted.dupe_id , inserted.name INTO @idents SELECT 'Peter Parker' UNION ALL SELECT 'Barry Allen' -- The above statement's trigger correctly spits out the rows we emit in the final -- step of the trigger -- dupe_id name -- 7 Barry Allen -- 1 Peter Parker -- Look at this, it's the inserted virtual table -- from the trigger in pristine condition -- and there's no way to unbugger it SELECT * FROM @idents I
Results
dupe_id name ----------- -------------------------------------------------- 1 Peter Parker six_of_one half_dozen_of_other current_identity_value --------------------------------------- --------------------------------------- --------------------------------------- 1 1 1 dupe_id name ----------- -------------------------------------------------- 2 Spider man six_of_one half_dozen_of_other current_identity_value --------------------------------------- --------------------------------------- --------------------------------------- 2 2 2 dupe_id name ----------- -------------------------------------------------- Duplicate key was ignored. this_identity_belongs_to_spider_man this_identity_also_belongs_to_spider_man current_identity_value --------------------------------------- ---------------------------------------- --------------------------------------- 2 2 3 dupe_id name $action ----------- -------------------------------------------------- ---------- 4 Hal Jordan INSERT hal_jordan still_hal_jordan current_identity_value --------------------------------------- --------------------------------------- --------------------------------------- 4 4 4 dupe_id name ----------- -------------------------------------------------- 5 Tony Stark tony_stark still_tony_stark current_identity_value --------------------------------------- --------------------------------------- --------------------------------------- 5 5 5 dupe_id name $action ----------- -------------------------------------------------- ---------- 4 Hal Jordan UPDATE tony_stark still_tony_stark current_identity_value --------------------------------------- --------------------------------------- --------------------------------------- 5 5 5 Duplicate key was ignored. dupe_id name ----------- -------------------------------------------------- 7 Barry Allen 1 Peter Parker dupe_id name ----------- -------------------------------------------------- 0 Peter Parker 0 Barry Allen
