IDENTITY Column Property

IDENTITY Column:

We can implement identifier columns by using the IDENTITY property. This enables us to specify both an identity number for the first row inserted into the table (Identity Seed ) and an increment (Identity Increment) to be added to the seed to determine successive identity numbers.

When values are inserted into a table that has an identifier column, the Database Engine automatically generates the next identity value by adding the increment to the seed.

If the table is already created and you want to add identifier then the identity numbers are added to the existing rows of the table, with the seed and increment values applied in the order that those rows were originally inserted. Identity numbers are also generated for any new rows that are added.

Now we are going to create a table with an identity column.

CREATE TABLE IDENTIFIER (ROLL_NO INT IDENTITY(1,1),NAME CHAR(12),ADDRESS VARCHAR(23))

INSERT INTO IDENTIFIER VALUES('VIKASH','GHAZIABAD'), ('AMIT','GHAZIABAD'),('VIKRAM','NOIDA'),('AJAY','DELHI')

Inserting value using row terminator (,) will only work in 2008 version or later if you are using SQL server 2005 then it will not work and you have to write whole statement.

Notice one think that we are not explicitly providing value for identity column in the insert statement. If we will do so we will get such a beautiful error as follows:

So from error it is clear that to do so we must have INDENTITY_INSERT ON.

How to do that we will see later in this article. First let’s see the output for the table which we have created that is IDENTIFIER.

SELECT * FROM IDENTIFIER

A table can have only one column defined with the IDENTITY property, and that column must be defined by using a decimal, int, numeric, smallint, bigint, or tinyint data type.

The seed and increment can be specified. The default value for both is 1.

The OBJECTPROPERTY function can be used to determine whether a table has an IDENTITY column, and the COLUMNPROPERTY function can be used to determine the name of the IDENTITY column.

Now let see how we can set identity_insert On. We can use the command

SET IDENTITY_INSERT [IDENTIFIER] ON

Now let’s see the tabel records

SELECT * FROM IDENTIFIER

It is good practice that you should set OFF it after using this. i.e.

SET IDENTITY_INSERT [IDENTIFIER] OFF.

Note:

If an identifier column exists for a table that has frequent deletions, gaps can occur between identity values. Deleted identity values are not reused. To avoid such gaps, do not use the IDENTITY property. Instead, you can create a trigger that determines a new identifier value, based on existing values in the identifier column, as rows are inserted.

To reuse the identity value you can also truncate the table but you never want to do this except some special cases.

This is one major difference between DELETE and TRUNCATE that if we will perform DELETE on identity column the Deleted identity values are not reused but if we will perform TRUNCATE then it will reuse the value .

In below figure first we have deleted the record for Roll_no=3. After that we inserted a new record. See the output in figure:

Let’s see the output after truncating and then inserting the records into table.

Now insert new records in same table and see the values of Identity column.

insert into identifier values('Ajit','NEW DELHI'),('ajay','NEW DELHI'),('hAPPY','NEW DELHI')

SELECT * FROM IDENTIFIER

So, this was all about identity column.

Please write in comment box to share your knowledge about IDENTITY Property.

Thanks!!!!