I'm using ADO.NET to try to get the value I'm about to insert before I insert it in SQL Server 2005. SCOPE_IDENTITY() works fine after, but I'm also inside a transaction if that makes a difference. I literally need to select the next ID, and only the ID and have it available to C# before the insert.
A:
The SCOPE_IDENTITY will only work once you've inserted the row in to the table. If you need to know the value before you insert you will need to use IDENT_CURRENT('table_name'). See http://msdn.microsoft.com/en-us/library/ms175098.aspx for more information.
Chuck Haines
2010-10-02 13:41:04
Thanks, but so far I've tried Ident_Current('mytable'), SELECT @newId = SCOPE_IDENTITY() FROM mytable, SELECT @newId = Ident_Current('CustomizedProducts) and directly selecting the ID without assigning it to an out parameter. I get a NULL every time.
Echilon
2010-10-02 13:47:42
ident_current is dangerous to use for this, the actual transaction may not end up with the next available identity as there may be other transactions that happen or even rollbacks. Very bad idea.
HLGEM
2010-10-02 20:21:06
Agreed that ident_current is dangerous but that's the best way even though it's not reliable or safe. There really is not way to get the value before an insert unless you don't use an identity and instead use a manual method.
Chuck Haines
2010-10-02 22:21:19
Thanks Chuck. Not what I wanted, but I'll need to think of a way around it.
Echilon
2010-10-03 06:17:00
unsafe: you absolutely need to serialize before you can safely use IDENT_CURRENT
AlexKuznetsov
2010-10-04 20:50:56
A:
try using IDENT_CURRENT. As MSDN says, "it returns the last identity value generated for a specific table in any session and any scope"
odiseh
2010-10-02 13:48:23
use with care, though! Even though you might know the last generated value, there is **NO GUARANTEE** you can guess the next one that will be used from this!
marc_s
2010-10-02 14:10:23
+2
A:
Whatever solution you choose, make sure to test it under high concurrency. most likely it will break - two or more different connections will get the same ID before they insert. Before doing anything, I would use sp_getapplock to serialize access, so that there is no concurrency.
AlexKuznetsov
2010-10-02 18:44:22
+1
A:
As an alternative solution you could change your primary key type to a GUID type, this would then allow you to insert with a known key.
Guid pk = Guid.NewGuid();
I dont recall the method for SQL server if the class is directly supported or if you need to convert it to a byte[]. For oracle we convert to a char(32).
This way you dont need to worry about concurrency.
Thomas James
2010-10-02 22:24:17
The Guid type is supported in in SQL server; it is called `uniqueidentifier`.
Kirk Broadhurst
2010-10-03 11:10:18