SQL Server IDENTITY Value and Its Use

Sometimes we need last identity value for a row inserted in a table. For Example you are creating a request table as

CREATE TABLE TBL_REQUEST

(

REQID INT IDENTITY(1,1) PRIMARY KEY,

REQNAME VARCHAR(100),

REQ_SUBJECT VARCHAR(200),

REQ_DESCRIPTION VARCHAR(2000),

REQ_DATE DATETIME

)

And you want to return a request no. for User for future refrence.In this scenario, you need  to return last inserted identity value to the user.

There are three ways to get last identity value

•      Using @@IDENTITY: Its scope is global. It returns identity value with respect to last inserted row in any table on Connection.

INSERT INTO TBL_REQUEST (REQNAME,REQ_SUBJECT,REQ_DESCRIPTION,REQ_DATE )

VALUES ('Uma','Regrading File Issue','Kindly Issue File HHH/123','4/10/2013' )

SELECT  @@Identity

Output:

3

If another insert take place after you insert, It will returns identity of latest insert table.

Ex:

Query 1:

INSERT INTO TBL_REQUEST (REQNAME,REQ_SUBJECT,REQ_DESCRIPTION,REQ_DATE )

VALUES ('Uma','Regrading File Issue','Kindly Issue File HHH/123','4/10/2013' )

 Query 2:

INSERT INTO TBL_REQUEST_History (REQNAME,REQ_SUBJECT,REQ_DESCRIPTION,REQ_DATE )

VALUES ('Uma','Regrading File Issue','Kindly Issue File HHH/123','4/10/2013' )

If you are exceuting first query and then execute SELECT  @@Identity But in between any body exceute Query 2.then SELECT  @@Identity Returns identity of TBL_REQUEST_History.

•     Using  SCOPE_IDENTITY():It returns latest identity value in same scope(as Stored Procedure,function,trigger or batch).

Suppose you create a procedure that also return identity by insert query executed with in stored procedure.

CREATE PROC USP_INSERT_REQ

(

@reqid int output,

@reqname varchar(200),

@reqsub varchar(200),

@desc varchar(2000)

)

as

BEGIN

INSERT INTO TBL_REQUEST(REQNAME,REQ_SUBJECT,REQ_DESCRIPTION,REQ_DATE)

VALUES(@reqname,@reqsub,@desc,GETDATE())

SET @reqid=SCOPE_IDENTITY()

      END

It returns inserted identity value by USP_INSERT_REQ stored procedure.You can use return request id as refrence no for Requester.

In C#,Use this code to show returns Identity to user on Label.

protected void Button1_Click(object sender, EventArgs e)

    {

cnn.Open();

SqlCommand cmd = new                       SqlCommand("usp_INSERT_REQ", cnn);

cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.AddWithValue("@reqname ", txtReq.Text);

cmd.Parameters.AddWithValue("@reqsub", txtSub.Text);

cmd.Parameters.AddWithValue("@desc", txtdesc.Text);

SqlParameter sp = new SqlParameter();

sp = new SqlParameter("@reqid", SqlDbType.Int);

sp.Direction = ParameterDirection.Output;

cmd.Parameters.Add(sp);

cmd.ExecuteReader();

Label1.Text = "Your refrence Number is " + cmd.Parameters["@kid"].Value.ToString() + ".Please use this for all communications regrading this request";

  }

•      Using  IDENT_CURRENT(table_name):(For particular table)It returns last identity value for defined table.

Ex:

Query1:
 INSERT INTO TBL_REQUEST (REQNAME,REQ_SUBJECT,REQ_DESCRIPTION,REQ_DATE )

VALUES ('Uma','Regrading File Issue','Kindly Issue File HHH/123','4/10/2013' )

Query2: 
INSERT INTO TBL_REQUEST_History (REQNAME,REQ_SUBJECT,REQ_DESCRIPTION,REQ_DATE )

VALUES ('Uma','Regrading File Issue','Kindly Issue File HHH/123','4/10/2013' )

SELECT  @@Identity: return identity value inserted by Query 2.

select IDENT_CURRENT('TBL_REQUEST') :return identity value inserted by query one.