Home > SQL Server > Identity(Property) and Identity(Function) – IDENTITY – SQL Server

Identity(Property) and Identity(Function) – IDENTITY – SQL Server


Identity(Property) : It is a table property settings to define identity column in a table definition. This property carries 2 arguments Seed and Increment. A table can have only one identity column. We can use this property with Create Table and Alter Table statements.

Identity(Property) Syntax
IDENTITY [ (seed , increment) ]

Seed: It is the starting value for identity column

Increment : It is the incremental value for identity column

The default identity column value if we do not give seed and increment value is Identity(1,1)

Example

As I told you already that we cannot have more than one identity column in a table. See this example given below.

pic1Whereas, if you use GUI table design to set column identity property, it will not throw error when you try to set multiple identity column property. Instead, it removes the identity property from the previously defined column and set the identity property to new column.

IDENTITY (Function): This function look similar to property. But this identity function is used to generate identity value using SELECT INTO statement and cannot be used with any other statement.

Identity(function) Syntax

IDENTITY (data_type [ , seed , increment ] ) AS column_name

Data type : this is the integer data type name other than bit and decimal

Seed : Identity start value

Increment: Identity incremental value

Example

I have already created a table with an identity column and inserted five rows as shown in the below image.

Source Table

pic2

Now, I am going to use identity(function) to transfer the data from the source table to new destination table with new identity values.

This SQL query will migrate data from source to destination table with new identity values.

SELECT identity(int, 1, 2) as NewRowid, code into tblNewIdentTable from tblIdentity
GO

pic3

Output

pic4

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: