Identity vs Scope Identity vs Ident_Current

-
While doing some update on an existing Store Procedure I came across a code that retrieves an Identity value.


1
2
3
4
5
6
if @@rowcount>0
begin
   select @id=scope_identity()
end
else
  select @id=0

Now let's discuss when to use Identity, Scope Identity and Ident_Current, I checked the internet and found an article from the SQL Authority. Here's a brief of what it says:

Select @@IDENTITY
It returns the identity value generated on a connection regardless of table and the scope which produce the value, it means that in case a trigger was also called from the same connection and produced an identity, this identity will be returned.

 Select SCOPE_IDENTITY
It returns the identity value generated on a connection and by a statement on the same scope regardless of the table the produced the value. it will return the value that was explicitly created by the statement and will not be affected by any trigger even if it creates an identity on the same table thus limiting the scope.

Select IDENT_CURRENT(tablename)
It returns the identity value generated from a table regardless of the scope or connection, it's not limited by sope and session, as long as an identity was produced by the same table it was operating on it will return it.

As a best practice mentioned by Pinal Dave of SQL Authority it's recommended to use Scope_Identity to avoid any issues with Triggers later on.



Comments

Post a Comment

Popular posts from this blog

Serializing JSON string to ExpandoObject

-
In some case you want to create and Object from JSON, a great way of doing this to deserialize this JSON to an ExpandoObject. 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 /// <summary> /// Convert Json String to an Expando Object /// </summary> /// <param name="that">Json string to convert</param> /// <returns>Expando Object</returns> public static ExpandoObject ToExpando ( this string that) { ExpandoObject result; try { result = JsonConvert.DeserializeObject<ExpandoObject>(that, new ExpandoObjectConverter()); } catch (Exception e) { throw e; } return result; } Here we use JsonConvert.DeserializeObject, you need to add reference to NewtonSoft.Json see below. 1 2 3 using Newtonsoft.Json ; using Newtonsoft.Json.Converters ; using Newtonsoft.Json.Linq ;
Read more

Adding an Obsolete Attribute to Class Methods in C#

-
As a developer we normally maintain our own framework, our own abstraction of things, be it database wrappers, our own implementations of persistence managers, in some cases we figure out a better way or new way of doing things so we decide to include it in the framework, how do we now tell other developers to use this instead of the old one? The answer is the Obsolete Attribute. 1 2 3 4 5 6 7 8 9 [Obsolete("ExpandoObject.CreateObject is deprecated, please use NameValueCollection.CreateObject instead.", true)] public static void CreateObject ( this ExpandoObject that, NameValueCollection querystring) { querystring.AllKeys.ToList().ForEach(x => { if (!that.ToDictionary(y => y.Key).ContainsKey(x)) ((IDictionary< string , object >)that)[x] = querystring[x]; }); } We simply add this attribute on top of the method or class and specify our message. BTW, the Boolean parameter if set to True, will throw and exceptio...
Read more