RSS

Tag Archives: IDENTITY

Identity column in SQL Server databases, SQL Server, T-SQL, Interview Question, Tips

Retrieving id of inserted record: What to use @@IDENTITY or SCOPE_IDENTITY() or OUTPUT

Hi guys,
This seems very basic information but need to know properly.

Issue:
I need to find out or have the id of the records which I inserted in table.

Analysis:
This requirement seems very simple but there could be various cases where you would not get the correct id just by using @@IDENTITY function.

Think of the case where you are inserting a record in table A and there is a trigger which fires as soon as there is a insert in table A which insert a record in table B. so in such case @@IDENTITY wont give you the correct id/pointer of inserted record.

So to deal with this issue you can use SCOPE_IDENTITY(). You will always get the last value you have inserted in table. However, it is noticed that sometimes in some scenarios SCOPE_IDENTITY() does not promise the correctness of results. May be due to parallelism or multiple inserts or bulk inserts in table(s). E.g. when you do … Insert Into Select <columns> from Table

Well, Microsoft recommends that “You do not use either of these functions in your queries when parallel plans are involved as they are not always reliable. Instead use the OUTPUT clause of INSERT statement to retrieve the identity value“.

Check out MS articles:
http://support.microsoft.com/default.aspx?scid=kb;en-US;2019779
http://msdn.microsoft.com/en-us/library/ms177564.aspx

I hope this will help you somewhere.
Thanks, Khilit

 

Tags: , , , , , ,

What happens to IDENTITY Column Value When SQL Transaction Fails??

Hi All,

I would like to share a simple interview question. This is very simple yet many developers overlooks or not taking care of it.

Do you know what happens to identity value if transaction fails? Do you think that identity value of table is unchanged after failure?

Before looking at the solution below, try to memorize ACID properties of transaction and answer it.

Check-out the POC shown below & wobble your mind :)

Read the rest of this entry »

 

Tags: , , ,