SQL Server Articles

Submit Article
Home » Articles » SQL Server » System FunctionsRSS Feeds

IDENTITY() Function

Posted By: Sarita Patel     Category: SQL Server     Views: 2057

This article explains about IDENTITY() function in sql server with examples.

IDENTITY() function is used only in a SELECT statement with an INTO table clause to insert an identity column into a new table.

This function creates a column in a table, a name for the column must be specified in the select list. 

Syntax of IDENTITY() Function :

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

data_type is a data type of an identity column. 

seed is the value to be assigned to the first row in the table. Each subsequent row is assigned the next identity value, which is equal to the last IDENTITY value plus the increment value. 

increment t
s the increment to add to the seed value for successive rows in the table.

column_name is the name of the column that is to be inserted into the new table.

Return type of IDENTITY() function is same as data_type.

Examples of IDENTITY() Function :

Example 1 : Use of IDENTITY() function in select clause 

SELECT IDENTITY(smallint, 1, 1) AS RegionID, RegionDescription
INTO   Region
FROM   Regions


RegionID   RegionDescription
1              Eastern
2              Western
3              Northern
4              Southern

Above example inserts all rows from the Regions table into a new table called Region. The IDENTITY function is used to start identification numbers at 1.


Didn't find what you were looking for? Find more on IDENTITY() Function Or get search suggestion and latest updates.

Sarita Patel
Sarita Patel author of IDENTITY() Function is from United States.
View All Articles

Related Articles and Code:

Please enter your Comment

  • Comment should be atleast 30 Characters.
  • Please put code inside [Code] your code [/Code].

No Comment Found, Be the First to post comment!