SQL SERVER BASICS TUTORIAL-PART 3

/*

  GETTING LAST GENERATED IDENTITY COLUMN VALUE FROM THE TABLE:-

               WE CAN GET THE LAST GENERATED IDENTITY COLUMN VALUE FOR A TABLE BY USING 

     1)SCOPE_IDENTITY() :-IT WILL RETURN LAST GENERATED IDENTITY VALUE FOR THE CURRENT SESSION AND CURRENT SCOPE

     2)@@IDENTITY :- IT WILL RETURN LAST GENERATED IDENTITY VALUE FOR THE CURRENT SESSION ACROSS ALL SCOPES.

     3)IDENT_CURRENT('TABLENAME'):- IT WILL RETURN LAST GENERATED IDENTITY VALUE FOR THE SPECIFIC TABLE IN ALL SESSION ACROSS ALL SCOPES.

     
     NOTE: SESSION IS NOTHING BUT IF YOU OPEN THE NEW QUERY WINDOW , THEN ONE SESSION WILL BE STARTED.


*/

--------------------------------------------------------------------------------------------------------------------------

CREATE TWO TABLES WITH IDENTITY COLUMN AS SHOWN BELOW:--


CREATE TABLE IDENTITYTEST1(ID INT IDENTITY(1,1),NAME VARCHAR(50))

CREATE TABLE IDENTITYTEST2(ID INT IDENTITY(1,1),NAME VARCHAR(50))

--------------------------------------------------------------------------------------------------------------------------

NOW INSERT SOME RECORDS INTO THE IDENTITYTEST1 :-

INSERT IDENTITYTEST1 VALUES('NAME1')
INSERT IDENTITYTEST1 VALUES('NAME2')

--------------------------------------------------------------------------------------------------------------------------

       NOW SELECT RECORDS FROM IDENTITYTEST1:--

SELECT * FROM IDENTITYTEST1


/*  RESULT:-

ID NAME
1 NAME1
2 NAME2

   BY LOOKING AT RESULT, WE CAN SAY THAT THE LAST GENERATED IDENTITY VALUE IS 2...IF YOU WANT TO GET THAT LAST IDENTITY VALUE, WE CAN GET IT AS SHOWN BELOW...

*/

--------------------------------------------------------------------------------------------------------------------------

TO GET THE  LAST IDENTITY VALUE FOR IDENTITYTEST1 RUN THE FOLLOWING COMMAND:-

SELECT SCOPE_IDENTITY() 'SCOPEIDENTITY'

/*
    RESULT:-

SCOPEIDENTITY
  2

*/

SELECT @@IDENTITY  '@@IDENTITY'
/*

 RESULT:-

@@IDENTITY
 2
*/

SELECT IDENT_CURRENT('IDENTITYTEST1') 'IDENT_CURRENT'

/*
RESULT:-

IDENT_CURRENT
 2

*/


--------------------------------------------------------------------------------------------------------------------------

/*
  NOW YOU MAY ASK SOMETHING LIKE THIS..WHAT IS THE DIFFERENCE BETWEEN THEM AND WHAT TO USE WHEN?

  PLS REFER THE TOP OF THIS DOCUMENT TO KNOW WHAT IS SCOPE_IDENTITY,@@IDENTITY AND IDENT_CURRENT  BEFORE PROCEEDING.

         TO UNDERSTAND BETTER,I AM GOING TO CREATE ONE TRIGGER ON IDENTITYTEST1 TABLE.WHENEVER WE INSERT SOME RECORDS INTO THE IDENTITYTEST1 , THIS TRIGGER WILL FIRE  AND INSERT ONE RECORD INTO THE IDENTITYTEST2 TABLE AS WELL.

  NOTE: TRIGGER IS NOTHING BUT IT WILL FIRE AUTOMATICALLY WHEN WE PERFORM INSERT ,UPDATE OR DELETE OPERATION..

*/

--------------------------------------------------------------------------------------------------------------------------
    CREATING A TRIGGER ON IDENTITYTEST1 TABLE:-


CREATE TRIGGER trIDENTITYTEST ON IDENTITYTEST1 FOR INSERT
AS
BEGIN
  INSERT IDENTITYTEST2 VALUES('Name for IDENTITYTEST2')
END


     NOW INSERT SOME RECORDS INTO THE IDENTITYTEST1:-



INSERT IDENTITYTEST1 VALUES('NAME3')

--RUN THE BELOW COMMAN AND SEE THE RESULT...
SELECT * FROM IDENTITYTEST1
SELECT * FROM IDENTITYTEST2

/*  RESULT FOR IDENTITYTEST1:-

ID NAME
1 NAME1
2 NAME2
3 NAME3

*/


/*

RESULT FOR IDENTITYTEST2:-

1 Name for IDENTITYTEST2

*/
--------------------------------------------------------------------------------------------------------------------------
NOW RUN THE FOLLOWING COMMAND WITH IN THIS SAME QUERY WINDOW:-

SELECT SCOPE_IDENTITY() 'SCOPEIDENTITY'

/*  RESULT:-
SCOPEIDENTITY
  3
*/

SELECT @@IDENTITY  '@@IDENTITY'
/*  RESULT:-
@@IDENTITY
 1
*/

SELECT IDENT_CURRENT('IDENTITYTEST1') 'IDENT_CURRENT'

/*  RESULT:-
IDENT_CURRENT
   3
*/

--------------------------------------------------------------------------------------------------------------------------

/*

 
   SO FAR WHAT I HAVE DONE IS I CREATED A TRIGGER ON IDENTITYTEST1. WHENVER WE INSERT SOME RECORDS INTO IDENTITYTEST1 ,THE TRIGGER WILL FIRE AND  INSERT ONE RECORD INTO THE IDENTITYTEST2 .

   ACTUALLY FIRST THING WE ARE DOING HERE IS WE ARE INSERTING SOME RECORDS INTO IDENTITYTEST1 WITHIN THE SAME WINDOW(I.E.IN CUREENT SESSION AND CURRENT SCOPE).

   THAT'S WHY SCOPE_IDENTITY() RETURNS 3 AS LAST IDENTITY VALUE WHICH IS NOTHING BUT LAST IDENTITY VALUE OF IDENTITYTEST1.
 
   AFTER THE  INSERT HAPPENED IN THE IDENTITYTEST1 ONLY, THE TRIGGER WILL BE FIRED AND INSERT A RECORD INTO THE  IDENTITYTEST2 TABLE.(I.E IN CUREENT SESSION AND ACROSS ALL SCOPE)

   INSTEAD OF SAYING THIS AS ALL SCOPE WE MAY SAY AS LAST SCOPE OF THIS SESSION.
 
 
 
*/

--------------------------------------------------------------------------------------------------------------------------

IDENT_CURRENT('TABLENAME'):- RETURN LAST GENERATED IDENTITY VALUE FOR THE SPECIFIC TABLE IN ALL SESSION(I.E. ALL OPEN QUERY WINDOW) ACROSS ALL SCOPES.
 
   SELECT IDENT_CURRENT('IDENTITYTEST2') 'IDENT_CURRENT'
 
   /*
   RESULT:-
   IDENT_CURRENT
   1
   */
 
   NOW INSERT SOME RECORDS INTO THE IDENTITYTEST2 FROM THE SAME WINDOW.
 
 
   INSERT IDENTITYTEST2 VALUES('Name for IDENTITYTEST2')
 
   SELECT IDENT_CURRENT('IDENTITYTEST2') 'IDENT_CURRENT'
 
    /*
   RESULT:-
   IDENT_CURRENT
     2
   */
 
 
   NOW OPEN THE NEW QUERY WINDOW (I.E.STARTING THE NEW SESSION) AND RUN THE FOLLOWING COMMAND.

   INSERT IDENTITYTEST2 VALUES('Name for IDENTITYTEST2')

   SELECT IDENT_CURRENT('IDENTITYTEST2') 'IDENT_CURRENT'

   /*
   RESULT:-
   IDENT_CURRENT
    3
   */
 
      AGAIN COME BACK TO STARTING WINDOW THEN RUN THE FOLLOWING COMMAND.
 
   SELECT IDENT_CURRENT('IDENTITYTEST2') 'IDENT_CURRENT'

   /*
   RESULT:-
   IDENT_CURRENT
    3
   */
 
   /*
 
   NOW U CAN UNDERSTAND TAHT IDENT_CURRENT WILL RETURN THE LAST GENERATED IDENTITY VALUE FOR THE SPECIFIC TABLE IN ALL SESSION(I.E. ALL OPEN QUERY WINDOW) ACROSS ALL SCOPES
   
   */
   ------------------------------------------------------------------------------------------------------------------------