SQL Interview: 96 Can an IDENTITY column contain duplicate values?

This is a post in the SQL Interview series. These aren’t trick or gotcha questions, they’re just questions designed to scope out a candidate’s knowledge around SQL Server and Azure SQL Database.

Section: Development Level: Medium

Question:

You have created a table named dbo.Customers. The column CustomerID has been declared as

INT NOT NULL IDENTITY(1, 1)

No contraints are defined on the table.

Can the CustomerID contain a duplicate value? If so, how?

Answer:

Yes, nothing about an IDENTITY column indicates that it is unique. In many cases, those columns are also primary keys but this table has no constraints. If there was a primary key constraint, or a unique contraint, then duplicate values could be be present if the constraint was flagged as trusted.

To insert values directly into an IDENTITY column, duplicated or not, you can use: