IDENTITY Column allows duplicate values!

Many new SQL developers/DBA's are unaware of actual properties of IDENTITY column. Earlier when I was into SQL learning, I had a column with IDENTITY constraint. I thought it would allow only unique values. But surprisingly I was able to insert duplicates using SET IDENTITY_INSERT statement. Then HERE I found the actual property of IDENTITY. Uniqueness must be enforced by using a PRIMARY KEY or UNIQUE constraint or UNIQUE index.

Here I have depicted with an example

CREATE TABLE Mytable(ID int IDENTITY(1,1), City Varchar(50))

INSERT INTO Mytable(City) Values('Bangalore')
INSERT INTO Mytable(City) Values('Mumbai')
INSERT INTO Mytable(City) Values('Hyderabad')

SELECT * FROM Mytable

ID	City
1	Bangalore
2	Mumbai
3	Hyderabad

Then with the help of SET IDENTITY_INSERT statement we can forcefully insert value into IDENTITY column ID.

SET IDENTITY_INSERT Mytable ON
INSERT INTO Mytable(ID,City) Values(2,'Cochin')

ID	City
1	Bangalore
2	Mumbai
3	Hyderabad
2	Cochin

Note

We cannot insert NULL values into IDENTITY column. It will throw below error if we try doing so.

INSERT INTO Mytable(ID,City) Values(NULL,'Chennai')

Msg 339, Level 16, State 1, Line 1
DEFAULT or NULL are not allowed as explicit identity values.