What are @@IDENTITY, SCOPE_IDENTITY and IDENT_CURRENT? What are the differences?

They allow us to find the last identity value produced when working with tables containing identity columns.

Select @@IDENTITY returns the last identity generated in connection, regardless of table and scope.

SELECT SCOPE_IDENTITY() Returns the recently generated identity value on the opened connection and on the periscope on which it runs in the query.

SELECT IDENT_CURRENT(Tablename) Returns the last identity generated in the given table as a parameter, regardless of connection and scope.

CREATE TABLE DP_TABLE1

(ID INT NOT NULL IDENTITY (1, 1),

Ad VARCHAR(50) NULL,

Soyad VARCHAR(50) NULL)

CREATE TABLE DP_TABLE2

(ID INT NOT NULL IDENTITY (1, 1),

KullanıcıId int,

ProfilName VARCHAR(50) NULL)

— İki tablo oluşturuyoruz.

INSERT INTO DP_TABLE1 VALUES

(‘Oğuzhan’, ‘Yılmaz’)

create TRIGGER DP_TRG ON DP_TABLE1

FOR INSERT

AS

INSERT INTO DP_TABLE2 VALUES

(@@IDENTITY, (SELECT lower(Ad) from inserted )+ (SELECT lower(left(Soyad,1)) from inserted))

INSERT INTO DP_TABLE1 VALUES

(‘Melih’, ‘Yıldırım’)

/*Idler de farklılık olması için önce birinci tabloya kayıt ekliyoruz

sonra trigger oluşturup ikinci tabloya kayıt ekliyoruz*/

select * from DP_TABLE1

select * from DP_TABLE2

SELECT @@IDENTITY AS DEGER ,’@@IDENTITY’ OBJE

UNION ALL

SELECT SCOPE_IDENTITY() , ‘SCOPE_IDENTITY()’

UNION ALL

SELECT IDENT_CURRENT(‘DP_TABLE1’) ,’IDENT_CURRENT() FOR TABLE1'

UNION ALL

SELECT IDENT_CURRENT(‘DP_TABLE2’) ,’IDENT_CURRENT() FOR TABLE2'