Today's Question-2!!!

CREATE TABLE Playground.Characters(
 UniqueId INT IDENTITY(1,1) NOT NULL,
 CharacterName VARCHAR(30) NOT NULL,
);

SET IDENTITY_INSERT Playground.Characters ON;

INSERT INTO Playground.Characters (UniqueId, CharacterName) VALUES(1, 'Dick');
INSERT INTO Playground.Characters (UniqueId, CharacterName) VALUES(2, 'Jane');
INSERT INTO Playground.Characters (UniqueId, CharacterName) VALUES(2, 'Sally');
INSERT INTO Playground.Characters (UniqueId, CharacterName) VALUES(4, 'Spot');

SET IDENTITY_INSERT Playground.Characters OFF;

DROP TABLE Playground.Characters;

Answer:

No error message will result.

Reason:

No error message will result. The identity insert was set to ON for the table so you can insert explicit values. The "Violation of INDENTITY KEY constraint." is a fictitious message adapted from the real message "Violation of PRIMARY KEY constraint." However, the table did not specify a primary key. As such, a duplicate value -- the clone -- will be allowed in the UniqueId column. The INSERT statement did specify a column list. Otherwise the column-list-identity-insert error would result.

This question was inspired from the statement “… when you have IDENTITY_INSERT on you are able to insert multiple rows with the same identity column value, provided you don't have a constraint that restricts duplicate values in your identity column.” in the article “Generating Surrogate Keys Using an Identity Column in SQL Server” by Gregory A. Larsen.