@@identity vs @@ident_current 3

[JeffSabat]

we have just a little discussion in the ofc with @@identity vs @@ident_current... it's like this, i have a stored procedure define as..
CREATE PROCEDURE [uspInsertData]
@FldName varchar(1000),
@FldValue varchar(1000),
@tablename varchar(100)
AS
declare @sql varchar(8000)
set @sql = @sql + @FldName + &quot values('" + @fldValue + "')"
exec (@sql)
select @@Identity
go
...
According to my ofcmates, i should use @@ident_current instead of @@identity because it will create error in a multi-user environment.

my stand is since it is in a stored procedure, i would always have a guarantee that i will get return the correct identity... am i correct or should i concede to them and use @@ident_current instead of @@identity?

 

[SQLSister]

No you should use scope_identity(). @@Identity should never be used for this purpose becasue if you ever put a trigger on the table which inserts a record which has an identity that is the identity which is returned. Scope Identity will return the identitu created by the process you are running inthe stored procedure.

Ident_current is even more danger ous. This is what BOL says about it:
"Returns the last identity value generated for a specified table in any session and any scope."
Definitely would never use that in a multi-user environment.

 

[rdroske]

Not really an answer but interestingly I don't even seem to have an @@ident_current, must be new with SQL 2000 (I'm currently looking at my SQL 7).

Since SQL 7 doesn't even have it....

I'll take a look at a 2000 machine

 

[SQLSister]

There is no @@indent_current, it really is
IDENT_CURRENT('table_name')

I forgot to mention that in my last post.

 

[rdroske]

OK, couple points here.

First of all neither ident_current() nor scope_ident() seem to be available on SQL 7 so if that the platform of anyone reading this thread @@identity seems to be your only choice.

Second I have to believe that you are correct that multi-user or not has no relevance here since these are statements issued from a stored procedure.

It seeems to me that you have to be able to look at a stored procedure as if it were a single SQL statement that will be executed from beginning to end without other processes jumping in and changing things. If this weren't true there are tons of things you couldn't do, ie select the max value of a field and make later decisions based on it etc.

If I'm wrong about that someone please post and explain.

Anyways, even assuming that, there is still a problem with @@identity. The trigger issue that SQLSister mentioned. That is a real issue even if no triggers are currently defined on the table you are updating. Someone could add one later and your code could stop working or worse work incorrectly. That kind of thing is why triggers should be used as a last resort.

If a trigger was added that updated some other table that used Identity you would have a problem.

Seems to me either scope_identity() or ident_current('TableName') would fix that.


So basically your co-workers were correct but for the wrong reason?

 

[SQLSister]

Yes but ident_current will return the last identity inserted on the table not necessarily the one you inserted in the stored procedure. Ident_current should not ever be used to return the identity if you want the one you just inserted and you are in a multiple user environment unless you lock the table to further inserts during your processing. Unlike @@identity and scope_identity, it will return a value whether you have done an insert or not to a table within your procedure.

Books on line has some examples of how all this works that really show the difference between the three commands. But the bottom line is that if you want to return the dentity value you just inserted to use in inserting data to a related table, you must use scope_identity or you will eventually have data integrity problems. If SQL Server 7 does not have this function, I do not know what to tell you to do. The insidious part of this is that using the other two, you may think you are fine and may be fine for quite some period of time before the problem happens. And then you are not likely to notice right away, unfortunately.

 

[rdroske]

But the point is that whether multi user or not it is not possible for another users actions to interrupt the stored procedure.

If you assume that that can happen there are many many many things you couldn't do in a stored procedure.

My feeling is that SQL has to treat a stored procedure as if it were a single SQL statement (which it actually is
exec()).

 

[SQLSister]

I believe you should take some time to actually read about transaction processing before you make clearly uninformed statements. Programs don't work they way you want them to work, they work the way they were programmed to work. If things worked the way you think they do, then there would be no need for the transaction processing commands. Perhaps you should also read about locking and deadlocks. Mutliple users can in fact interfere with each other if you do not program correctly.

 

[rdroske]

Transaction processing commands (begin and end batch) is a different issue.

That has to do with being able to roll back transactions that have to succeed as a group.

It would also make a difference if the select statement was issued from a client outside of a stored procedure and then the ident_current() was issued.

In that case I would agree that it couldn't be relied upon.

I'm not saying I'm definitely correct only that it only seems logical. You will notice that in that post I said "if I am wrong...".

Nothing you have shown me yet has made me change my mind.

 

[rdroske]

OK, I'm wrong. At least I'm now pretty certain I was.

I couldn't find anywhere that said what I was thinking was correct so I started to have doubts.

It occurred to me that if I was correct and I executed an SP that was an infinite loop it would lock out all other processes.

It doesn't.

 

[JeffSabat]

thanks for that great discussion...it really answered my question. Actually, we have the same reason with rdroske where since it is a stored procedure then no other processes can interrupt it.
But thanks to SQL Sister...
point is taken. i'm going to share this to my ofcmates,
and i'm going to use scope_identity(), instead of @@identity and ident_current('tablename').
Again thank you.

 

[SQLSister]

rdroske, you get a star for testing to see if you were wrong and then admitting it. Sorry if I was snippy yesterday. I'm coming down with a cold and that makes me cranky.

 

[nigelrivett]

>> since it is a stored procedure then no other processes can interrupt it.

Haven't read the thread but I hope you don't think the server will execute an SP and not let anything else happen while it is executing.
The only thing you can rely on without coding for it is that single sql statements are atomic
Without locking resources (implicitly or explicitly) you can't assume that anything will happen without other threads executing.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[rdroske]

No problem at all SQLSister, thanks for pointing out the error of my ways. That could have come back to bite me big time one of these days.

 

[JeffSabat]

Hi,
ive tested my program instead of using @@identity, i use
scope_identity but the problem is it returns a null value.
I'm using vb.net in my development and in my program, I have a class for my database connection named clsDataAccess where it has a function insert defined as

public function insert(myData) as long
try
... here i do my connection to SQL which returns the identity
catch

end catch
end function

When @@identity is used, my program works fine, same with ident_current(tablename), but when scope_identity() is used it returns null value.
any comments pls....

 

[SQLSister]

I believe you need to run a stored procedure to get the data. (I don't know for sure becasue I do not allow any data access to my databases that is not from a stored procedure.)This returns data from the current scope, so if you execute as separate SQL statements, it would not consider that in the same batch, hence a null value. If there is a way to make the original insert statemetn and the select Scope_Identity() statement inthe same batch without using a stored proceure, I would try that.