IDENTITY() vs IDENTITY() in SQL Server: What’s the Difference?

by

It might seem strange, but T-SQL has an IDENTITY() property and an IDENTITY() function, each of which serve a (similar, but) different purpose.

  • The IDENTITY() property creates an identity column in a table. An identity column contains an automatically incrementing identification number. This property is used with the CREATE TABLE and ALTER TABLE statements.
  • The IDENTITY() function however, is used only in a SELECT statement with an INTO table clause to insert an identity column into a new table.

There’s also the SQL-DMO Identity property that exposes the row identity property of a column, but I won’t cover that here. Microsoft advises that this property will be removed in a future version of SQL Server, and that you should avoid using it in new development work.

IDENTITY() Property

Here’s an example of using the IDENTITY() property.

CREATE TABLE Pets (
    PetId int IDENTITY(1,1) PRIMARY KEY, 
    PetName varchar(255)
    );

This code creates a table with two columns. The first column (PetId) is an identity column (because I use the IDENTITY() property in its definition).

Now that I’ve created an identity column, I can insert pet names into the PetName column without needing to include an ID for each row.

INSERT INTO Pets (PetName)
VALUES ('Homer'), ('Felix'), ('Ruff');

SELECT * FROM Pet;

Result:

+---------+-----------+
| PetId   | PetName   |
|---------+-----------|
| 1       | Homer     |
| 2       | Felix     |
| 3       | Ruff      |
+---------+-----------+

In this case, the numbering starts at 1 and each row increments by 1. This is because I used IDENTITY(1,1) to specify that the seed value is 1 and that it is incremented by 1 on each new row.

IDENTITY() Function

As mentioned, the IDENTITY() function is used in a SELECT INTO statement. Here’s an example of using the IDENTITY() function.

SELECT 
  IDENTITY(int, 101, 10) AS pet_id,
  PetName AS pet_name
INTO BestFriends
FROM Pets;

SELECT * FROM BestFriends;

Result:

+----------+------------+
| pet_id   | pet_name   |
|----------+------------|
| 101      | Homer      |
| 111      | Felix      |
| 121      | Ruff       |
+----------+------------+

Here, I created a table called BestFriends and populated it from the Pets table.

In this case I started the count at 101 and incremented it by 10. Also note that the function requires the first argument to specify the data type of the column.