Difference between @@IDENTITY,SCOPE_IDENTITY() and IDENT_CURRENT

One of my friend and my colleague Mr Jithin asked me about the difference between @@IDENTITY,SCOPE_IDENTITY() and IDENT_CURRENT.During one of my  interviews it acts as a  question.

SELECT @@IDENTITY
It returns the last IDENTITY value(auto_increment value) produced on a connection, regardless of the table that produced the value, and regardless of the scope of the statement that produced the value.
@@IDENTITY will return the last identity value entered into a table in your current session. While @@IDENTITY is limited to the current session, it is not limited to the current scope. If you have a trigger on a table that causes an identity to be created in another table, you will get the identity that was created last, even if it was the trigger that created it.

SELECT SCOPE_IDENTITY()
It returns the last IDENTITY value produced on a connection and by a statement in the same scope, regardless of the table that produced the value.
SCOPE_IDENTITY(), like @@IDENTITY, will return the last identity value created in the current session, but it will also limit it to your current scope as well. In other words, it will return the last identity value that you explicitly created, rather than any identity that was created by a trigger or a user defined function.

SELECT IDENT_CURRENT(‘tablename’)
It returns the last IDENTITY value produced in a table, regardless of the connection that created the value, and regardless of the scope of the statement that produced the value.
IDENT_CURRENT is not limited by scope and session; it is limited to a specified table. IDENT_CURRENT returns the identity value generated for a specific table in any session and any scope.

Difference Between Varchar and Char in SQL Server

Two days before, One of my  colleague  who is  a .Net Programmer named Preethy Kamath (an enthusiastic lady)came and asked me what is the difference between char and varchar.  I explained her in detail about the difference and that is what I am going to explain here.

  • Char

It is fixed length non-Unicode character data with a length from 1 to 8000 bytes.

  • Varchar

It is variable length non-unicode character data with a length from 1 to 8000 bytes.The maximum storage size is 2^31-1 bytes.

From the columns varchardatalength  and Chardatalength we came to understand that the varchar takes only 3 character length(since it is variablelength) and Char takes 50 charlength(since it is  fixed length).

  • Conclusion

Please be careful   while choosing the data type during Database design .

I hope every one like this article,Heathly comments are welcomed.Thanks Preethi once again for this wonderful basic question.

Adding OR Removing IDENTITY To and From the Existing Column Using T-SQL

Recently while I am designing a Database, I came across a situation to add an identity to an Existing column. Here I am using a table Items. First I thought, there will be an alter command to do this. I wrote a DD L statement like this ALTER TABLE ITEMS ALTER COLUMN IDENTITY(1,1) and it is throwing an Error like ‘incorrect syntax’. So I started to solve the problem and at last I found an amazing answer i.e.; we can’t directly ADD or REMOVE an IDENTITY column using T-SQL.

How I Find out?

First I want to find what is happening inside SQL Server when we are adding or removing an Identity to an existing column using SQL Server Management Studio. For that, I enabled the Auto generate change script option in SQL Server Management Studio.

  • Enabling change script in SQL Server Management Studio

Choose Tools ->Options -> select designers, in that choose

Auto  generate change scripts option from the right box.

  • Adding an  IDENTITY to  an  Existing Column Using SSMS:

Select the column which we want to enable the Identity and save      the   process.While saving a window appears showing the T-SQL statements.

T-SQL Statements behind the scene are,

–For Enabliling Identity Column

/* To prevent any potential data loss issues, you should review this script in detail before running it outside the context of the database designer.*/

–For Enabliling Identity Column

/* To prevent any potential data loss issues, you should review this script in detail before running it outside the context of the database designer.*/

BEGIN TRANSACTION

SET QUOTED_IDENTIFIER ON

SET ARITHABORT ON

SET NUMERIC_ROUNDABORT OFF

SET CONCAT_NULL_YIELDS_NULL ON

SET ANSI_NULLS ON

SET ANSI_PADDING ON

SET ANSI_WARNINGS ON

COMMIT

BEGIN TRANSACTION

GO

CREATE TABLE dbo.Tmp_Items

(

ItemID int NOT NULL IDENTITY (1, 1),

ItemType int NULL,

ItemName varchar(50) NULL,

GenericID int NULL

)  ON [PRIMARY]

GO

SET IDENTITY_INSERT dbo.Tmp_Items ON

GO

IF EXISTS(SELECT * FROM dbo.Items)

EXEC(‘INSERT INTO dbo.Tmp_Items (ItemID, ItemType, ItemName, GenericID)

SELECT ItemID, ItemType, ItemName, GenericID FROM dbo.Items WITH (HOLDLOCK TABLOCKX)’)

GO

SET IDENTITY_INSERT dbo.Tmp_Items OFF

GO

DROP TABLE dbo.Items

GO

EXECUTE sp_rename N’dbo.Tmp_Items’, N’Items’, ‘OBJECT’

GO

ALTER TABLE dbo.Items ADD CONSTRAINT

PK_Items PRIMARY KEY CLUSTERED

(

ItemID

) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO

COMMIT

  • Removing an IDENTITY Column Using SSMS

T-SQL Statements behind the scene are,

/* To prevent any potential data loss issues, you should review this script in detail before running it outside the context of the database designer.*/

BEGIN TRANSACTION

SET QUOTED_IDENTIFIER ON

SET ARITHABORT ON

SET NUMERIC_ROUNDABORT OFF

SET CONCAT_NULL_YIELDS_NULL ON

SET ANSI_NULLS ON

SET ANSI_PADDING ON

SET ANSI_WARNINGS ON

COMMIT

BEGIN TRANSACTION

GO

CREATE TABLE dbo.Tmp_Items

(

ItemID int NOT NULL,

ItemType int NULL,

ItemName varchar(50) NULL,

GenericID int NULL

)  ON [PRIMARY]

GO

IF EXISTS(SELECT * FROM dbo.Items)

EXEC(‘INSERT INTO dbo.Tmp_Items (ItemID, ItemType, ItemName, GenericID)

SELECT ItemID, ItemType, ItemName, GenericID FROM dbo.Items WITH (HOLDLOCK TABLOCKX)’)

GO

DROP TABLE dbo.Items

GO

EXECUTE sp_rename N’dbo.Tmp_Items’, N’Items’, ‘OBJECT’

GO

ALTER TABLE dbo.Items ADD CONSTRAINT

PK_Items PRIMARY KEY CLUSTERED

(

ItemID

) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO

COMMIT

  • What we Observed ON Adding an IDENTITY To the Existing Table
  1. SQL Server first create a new table named dbo.Tmp_Items with an identity column and of course with same column names.
  2. Move the data from the Existing table (here table name is Items) to the new Tmp_Items table.
  3. Drop the Previous table (here table name is Items)
  4. Rename the Newly created table to the Old table name.
  5. At last create the indexes and Foreign-Key relationships.
  • What we Observed ON Removing  an IDENTITY From the Existing Table
  1. SQL Server first create a new table named dbo.Tmp_Items without an Identity column
  2. Move the data from the Existing table (here table name is Items) to the new Tmp_Items table.
  3. Drop the Previous table (here table name is Items)
  4. Rename the Newly created table to the Old table name using sp_rename.At last create the indexes and Foreign-Key relationships.

Hope Everyone like this Article,Healthy comments are welcomed.