The IDENTITY function can only be used when the SELECT statement has an INTO clause.

When we try to generate sequence number for existing records in a table, we have lot of ways...

But, can we use IDENTITY function for that ?

Yes. but, it can be used only when creating new table and copying records from an existing table using SELECT...INTO. otherwise, The following error will occur..

Scenario:
/*Creating a new table*/
If OBJECT_ID('Tb_Sample') Is Null
Create Table Tb_Sample
(
Sensitivity Varchar(25)
)
Go

/*Inserting some sample data*/
Insert Tb_Sample Values('Case')
Insert Tb_Sample Values('Accent')
Insert Tb_Sample Values('Kana')
Insert Tb_Sample Values('Width')
Go

/*Using IDENTITY function*/
Select Identity(Int,1,1) [Sequence],Sensitivity from Tb_Sample
Go

oh. It throws an error...!.

Msg 177, Level 15, State 1, Line 1
The IDENTITY function can only be used when the SELECT statement has an INTO clause.

The IDENTITY function shoule be used as follows instead.

Select Identity(Int,1,1) [Sequence],Sensitivity Into Tb_Sensitivity from Tb_Sample
Go

Select * from Tb_Sensitivity
Go

So, IDENTIFY function can be used with SELECT...INTO statement