Identity Function in SQL Server 2008

In this article I am going to explain how to use identity function in table.
  • 2540

Introduction

In this article I am going to explain about Identity () Function. This function is used to insert a automatically generated values in a given column.

Identity() Function

Identity() function is used to assign an automatically incremented number to a column. That column can be of int, bigint, decimal or smallint type.

Syntax

Column_Name Data_Type identity(m,n)

where,

Column_Name- is the name if the column.

Data_Type- is the data type of given column.

m- is the value to be assigned to the first row in the table.

n- is increment to add to the m value for next rows in the table.

Example

Creation of table

create table student(roll_num int identity(1,1), stu_Fname varchar(10), stu_Lname varchar(10))
insert into student
select 'a','b' union all
select 'c','d' union all
select 'e','f' union all
select 'g','h' union all
select 'i','j'
select * from student

Output:

Table-in-sql.jpg

Creating a new table using SELECT INTO where the identity value is needed in new table.

select
(
select
count (*)
from
student as T2
where
T2.stu_Fname <= T1.stu_Fname)
AS rownum,
stu_Fname
from
student as T1
order by
stu_Fname
drop table student
go
 

Output:

IdentityFunction.jpg

Categories

More Articles

© 2019 DotNetHeaven. All rights reserved.