Difference between @@IDENTITY, SCOPE_IDENTITY, IDENT_CURRENT

Compare

@@IDENTITY

It returns the last identity value generated for any table in the current session, across all scopes.

Let me explain this... suppose we create an insert trigger on table which inserts a row in another table with generate an identity column, then @@IDENTITY returns that identity record which is created by trigger.

SCOPE_IDENTITY

It returns the last identity value generated for any table in the current session and the current scope.

Let me explain this... suppose we create an insert trigger on table which inserts a row in another table with generate an identity column, then SCOPE_IDENTITY result is not affected but if a trigger or a user defined function is affected on the same table that produced the value returns that identity record thenSCOPE_IDENTITY returns that identity record which is created by trigger or a user defined function.

IDENT_CURRENT

It returns the last identity value generated for a specific table in any session and any scope.

In other words, we can say it is not affected by scope and session, it only depends on a particular table and returns that table related identity value which is generated in any session or scope.

SQL Query

I am explaining the above process with the help of some sample query, hope it helps:

CREATE TABLE Parent(id int IDENTITY);

CREATE TABLE Child(id int IDENTITY(100,1));

GO

CREATE TRIGGER Parentins ON Parent FOR INSERT

AS

BEGIN

   INSERT Child DEFAULT VALUES

END;

GO

--End of trigger definition

SELECT id FROM Parent;
--id is empty.

SELECT id FROM Child;
--ID is empty. 

--Do the following in Session 1
INSERT Parent DEFAULT VALUES;
SELECT @@IDENTITY;
/*Returns the value 100. This was inserted by the trigger.*/

SELECT SCOPE_IDENTITY();
/* Returns the value 1. This was inserted by the
INSERT statement two statements before this query.*/ 

SELECT IDENT_CURRENT('Child');

/* Returns value inserted into Child, that is in the trigger.*/

SELECT IDENT_CURRENT('Parent');

/* Returns value inserted into Parent. 
This was the INSERT statement four statements before this query.*/ 

-- Do the following in Session 2.

SELECT @@IDENTITY;

/* Returns NULL because there has been no INSERT action
up to this point in this session.*/ 

SELECT SCOPE_IDENTITY();

/* Returns NULL because there has been no INSERT action
up to this point in this scope in this session.*/

SELECT IDENT_CURRENT('Child');

/* Returns the last value inserted into Child.*/

Upload CSV file in sql server and dynamically create table with n number of columns

--======================================================================
--Script To Import CSV File In Sql Server.
--======================================================================
DECLARE @ColumnCount INT,
@tableName Nvarchar(150),
@Counter INT,
@Sql Nvarchar(Max),
@Path Nvarchar(150),
@MaxColname NVARCHAR(MAX),
@Colname NVARCHAR(MAX),
@FIELDTERMINATOR NVARCHAR(5),
@FileOK  INT,
@FileOK1  INT


--======================================================================
 --Mandaterory Variable to Supply    
--======================================================================
-- @ColumnCount  :  Supply the total column count.
-- @tableName   :  The table name which you want to Create.
-- @Path    :  The path of you file in local machine.If you are supplying
-- a local path then run it on your local SQL server instance.
-- If you want to run it on server then,place your file on any folder of
-- your server and supply its path.
-- @FIELDTERMINATOR    : Supply the field Delemeter.For example '|'.
--========================================================================

SET @ColumnCount=10
SET @tableName='FIP_Document_DataDump'
SET @Path='C:\Puneet\Projects\LinkedIn\CSV\FIP - Document Data Dump.csv'
SET @FIELDTERMINATOR='|'
SET @Counter=1
SET @Sql=''

BEGIN TRY

--=========================== Error Log File ===============================--
-- If there comes an error while importing CSV file,the error related to CSV
-- file will be stored into file 'ImportErrorData.log.Error.Txt' and
-- 'ImportErrorData.log'.These files will create automatically in C drive,when
-- any error in CSV file will occur.
--==========================================================================--

 exec xp_fileExist 'c:\ImportErrorData.log.Error.Txt' ,@FileOK OUTPUT
    exec xp_fileExist 'c:\ImportErrorData.log' ,@FileOK1 OUTPUT

 IF(ISNULL(@FileOK,0)=1 AND ISNULL(@FileOK1,0)=1)
    BEGIN
  ---- To allow advanced options to be changed.

  EXEC sp_configure 'show advanced options', 1


  -- To update the currently configured value for advanced options.

  RECONFIGURE

  -- To enable the feature.
        print 'test'
  EXEC sp_configure 'xp_cmdshell', 1

  -- To update the currently configured value for this feature.

  RECONFIGURE

        EXEC XP_CMDSHELL 'del C:\ImportErrorData.log.Error.Txt'
  EXEC XP_CMDSHELL 'del C:\ImportErrorData.log'
    END



 IF EXISTS(SELECT 1 FROM SYS.TABLES WHERE name=@tableName)
 BEGIN
   EXEC('DROP TABLE '+@tableName)
 END


 WHILE (@Counter<=@ColumnCount)
 BEGIN
  IF(@Sql='')
   BEGIN
    SET @Sql='CREATE TABLE TempTable
       (
     
       Column'+Cast(@Counter as varchar(20))+' Nvarchar(MAX),'


   END
    ELSE
   BEGIN

     IF(@Counter<@ColumnCount)
      SET @Sql = @Sql + ' Column'+Cast(@Counter as varchar(20))+' Nvarchar(MAX),'
     ELSE
      SET @Sql = @Sql + ' Column'+Cast(@Counter as varchar(20))+' Nvarchar(MAX) )'
   END
 
  SET @Counter=@Counter+1
 END

 EXECUTE(@Sql)
    print @Sql

 SET @Sql='
 BULK INSERT TempTable
 FROM '''+@Path+'''
 WITH
 (
 FIRSTROW = 1,
 -- DATAFILETYPE = ''char'',
 -- MAXERRORS = 100,
 FIELDTERMINATOR = '''+@FIELDTERMINATOR+''',
    ERRORFILE = ''C:\ImportErrorData.log''
 --ROWTERMINATOR = ''^''
 );'

 print @Sql
 EXEC(@Sql)


 Select ROW_NUMBER() OVER(ORDER BY (select 1)) ID,*  INTO #temp from TempTable


 SELECT @MaxColname=
 STUFF((
 select ',ISNULL(MAX(LEN('+b.Colname+')),50) '+b.Colname from
 (select 1 ID,name as Colname from sys.columns  where object_name(Object_Id)='TempTable') b
 WHERE b.id=a.id
 FOR XML PATH('')), 1, 1, '')
 from (select 1 ID,name as Colname from sys.columns  where object_name(Object_Id)='TempTable') a
 group by a.ID

 --print @MaxColname
 --order by CAST(REPLACE(name,'Column','') AS INT)

 SET @Sql='
 select 1 ID,'+ @MaxColname +'
 INTO ##tempMax
 FROM #temp WHERE ID!=1'

 EXEC(@Sql)
-- print @Sql


 --select * from ##tempMax




 SELECT @Colname=
 STUFF((
 select ','+b.Colname from
 (select 1 ID,name as Colname from sys.columns  where object_name(Object_Id)='TempTable') b
 WHERE b.id=a.id
 FOR XML PATH('')), 1, 1, '')
 from (select 1 ID,name as Colname from sys.columns  where object_name(Object_Id)='TempTable') a
 group by a.ID



 set @Sql
   = 'select ROW_NUMBER() OVER(ORDER BY (select 1)) ID, [Value] Val1
   INTO ##temp1
   from #temp
   unpivot
   (
   value
   for Col in ('+ @Colname +')
   ) u
  WHERE ID=1 '

 EXEC(@Sql)


 --select * from ##temp1

 set @Sql
   = 'select ROW_NUMBER() OVER(ORDER BY (select 1)) ID, [Value] Val2
   INTO ##temp2
   from ##tempMax
   unpivot
   (
   value
   for Col in ('+ @Colname +')
   ) u
  WHERE ID=1 '
 EXEC(@Sql)

 --select * from ##temp2





 SELECT @Sql='CREATE TABLE '+@tableName+' ('+
 STUFF((
 select ','+b.Colname from
 (
  Select 1 as ID,'['+Val1+ '] NVARCHAR('+ CASE WHEN Val2>8000 THEN 'MAX' ELSE CAST(Val2 as char(5)) END +')' as Colname
  FROM ##temp1 a
  INNER JOIN ##temp2 b
  ON a.ID=b.ID
 ) b
 WHERE b.id=a.id
 FOR XML PATH('')), 1, 1, '')+')'
 from (
   Select 1 as ID,'['+Val1+ '] NVARCHAR('+ CASE WHEN Val2>8000 THEN 'MAX' ELSE CAST(Val2 as char(5)) END +')' as Colname
   FROM ##temp1 a
   INNER JOIN ##temp2 b
   ON a.ID=b.ID
   ) a
 group by a.ID


 EXEC(@Sql)
-- print @Sql

 SET @Sql='INSERT INTO '+@tableName+'
     Select '+ REPLACE(REPLACE(REPLACE(REPLACE(@MaxColname,'MAX(LEN(',''),'))',' as '),'ISNULL(',''),',50)','')+' FROM #temp WHERE ID!=1'

 EXEC(@Sql)
-- PRINT @Sql
    EXEC('SELECT CAST(Count(1) as Nvarchar(max)) +'' Records Imported'' as '+@tableName+' FROM '+@tableName)

   
 
 DROP TABLE #temp
 DROP TABLE ##tempMax
 DROP TABLE ##temp1
 DROP TABLE ##temp2
 DROP TABLE TempTable
END TRY
BEGIN CATCH

   SELECT 'There was an error! ' + ERROR_MESSAGE()

   IF OBJECT_ID('tempdb..#temp') IS NOT NULL
   DROP TABLE #temp

   IF OBJECT_ID('tempdb..##tempMax') IS NOT NULL
   DROP TABLE ##tempMax

   IF OBJECT_ID('tempdb..##temp1') IS NOT NULL
   DROP TABLE ##temp1

   IF OBJECT_ID('tempdb..##temp2') IS NOT NULL
   DROP TABLE ##temp2

   IF OBJECT_ID('TempTable') IS NOT NULL
   DROP TABLE TempTable

END CATCH


--select * from Tmk_mdcTMMCL

--drop table Tmk_mdcTMMAC

Multiple NULL values in a Unique index in SQL

 That is allowed by Oracle but not in SQL Server and DB2 LUW. There is a way to make this work in SQL Server and DB2 LUW also but that requires a work-around. Consider this table:

CREATE TABLE TEST_UQ (COL1 INT IDENTITY(1,1) PRIMARY KEY, COL2 NVARCHAR(10) NULL)
GO

In this table, COL1 has been declared as the primary key but we want a UNIQUE
constraint to be put on COL2 as well. Please note that COL2 is a nullable column
and that SQL Server does not allow multiple NULL values in a UNIQUE index and treats
them the same way. We can test it out prior to proceeding with the work-around:

Let tus create a unique index first:

CREATE UNIQUE INDEX TEST_UQ_IND_1 ON TEST_UQ (COL2)
GO

Now, let us try to insert these values:

insert into test_uq (col2) values (’abc’);
insert into test_uq (col2) values (’xyz’);
insert into test_uq (col2) values (Null);

All three will go in. After that, try to insert the NULL value again:

insert into test_uq (col2) values (Null);

and you will get the error:

Msg 2601, Level 14, State 1, Line 1
Cannot insert duplicate key row in object ‘dbo.test_uq’ with unique index
‘TEST_UQ_IND_1′.
The statement has been terminated.

The work-around is to have a computed column and define the unique constraint on it.
 Here is how you can do that:

1) First, let’s drop the existing unique index:

drop index test_uq.TEST_UQ_IND_1

2) Next, let’s add the computed column:

ALTER TABLE TEST_UQ ADD COL3 AS (CASE WHEN COL2 IS NULL THEN CAST(COL1 AS NVARCHAR(10)) ELSE COL2 END);

In this command, we are stating that whenever the value for COL2 is null,
replace it with the primary key after casting it to the same data-type as that of COL2. By doing so, we will mae sure that COL3
 is always NOT NULL and always have unique values. This approach will work well in this case as there should never be a clash of
the values between COL1 and COL2. For example, what-if you needed to do this on a column that was also an interger data-type column? 
In that case, chances of clashes of the data can arise. If you suspect a clash, you can have additional logic like:
 (CASE WHEN COL2 IS NULL then -1 * COL1 ELSE COL2 END). That way, you can still maintain the logic and the uniqueness.

3) Now, create the unique index on this column:

CREATE UNIQUE INDEX TEST_UQ_IND_1 ON TEST_UQ (COL3)
GO

4) Next, let’s try to insert the NULL value again:

insert into test_uq (col2) values (Null);

This time it will go through. If we examine the contents of the table:

COL1        COL2       COL3
----------- ---------- ----------
1           abc        abc
2           xyz        xyz
3           NULL       3
5           NULL       5

As you can see, we have allowed multiple NULL values now for
COL2 and still maintained the uniqueness. We can next try to insert the value
“abc” again and see if that preserves our uniqueness criteria:

insert into test_uq (col2) values (’abc’);

This time, we will get an error:

Msg 2601, Level 14, State 1, Line 1
Cannot insert duplicate key row in object ‘dbo.test_uq’ with unique index ‘TEST_UQ_IND_1′.
The statement has been terminated.

So, using this work-around, one can preserve the same behavior as Oracle.
This might be useful to you as well in case you are working on a project
that requires conversion from Oracle to SQL Server or Oracle to DB2 LUW.

Get comma seperated values with help of XML Path

SELECT Column1,
 SUBSTRING(
 (
  SELECT ( ', ' + Column2)
  FROM #test1 t2
  WHERE t1.Column1 = t2.Column1
  ORDER BY t1.Column1, t2.Column1
  FOR XML PATH('')
 ), 3, 1000)
FROM #test1 t1
GROUP BY Column1