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

INSERT into an identity column not allowed on table variables.

Normally, Inserting data explicitly in IDENTITY column in Table variable is not allowed.

Declare @Table1 Table(ID Int Identity(1,1), Column1 Varchar(10))
Insert @Table1(Id, Column1) Values(1,'Sql Server')
Go

It'll throws an error
Msg 1077, Level 16, State 1, Line 2
INSERT into an identity column not allowed on table variables.

DEFAULT or NULL are not allowed as explicit identity values.

Normally, an IDENTITY column can not be a Nullable.

When we try to insert some explicit data into an IDENTITY column the following error occurred.

Create Table Tb_Table1
(
Id Int Identity(1,1),
Column1 int
)
Go

Set identity_insert Tb_Table1 on
Insert Tb_Table1 (Id,Column1) values(Null,1)
Set identity_insert Tb_Table1 off

It'll throw an error
Msg 339, Level 16, State 1, Line 2
DEFAULT or NULL are not allowed as explicit identity values.