2

I have a table access by multiple applications with multiple transactions and I want these multiple applications/transactions to get each Primary Key / Identity inserted accordingly by application who execute that transaction. For some reason I can't use SCOPE_IDENTITY that is why my last sort is to use @@IDENTITY. For transactional query I implemented IsolationLevel.Snapshot for each transaction to avoid blocking.

Now my question is, Will each transaction return the @@IDENTITY correctly and accordingly with this scenario.

Ex. I have three query execute simultaneously:

  • Transactional1 Query Insert with expected return Identity 100;
  • Transactional2 Query Insert with expected return Identity 102;
  • Transactional3 Query Insert with expected return Identity 103;

Will it return the Identity accordingly like this? this is my aim

  • Transaction1 - 100
  • Transaction2 - 102
  • Transaction3 - 103

Or it might happen like this? this is I'm afraid to happen

  • Transaction1 - 102
  • Transaction2 - 103
  • Transaction3 - 101

This existing trigger is the reason why SCOPE_IDENTITY does return identity even though I have SCOPE_IDENTITY in my code and query.

ALTER trigger [dbo].[CustomerAddressesInsertVIds] on [dbo].[CustomerAddresses]
instead of insert
as
begin
set nocount on
insert into [dbo].[CustomerAddresses]
([CustomerID], [AddressTypeID], [CustomerAddressID], [AddressNameType], [Name], [ContactID], [Address1], [Address2], [Address3], [City], [County], [State], [Country], [Zip], [Phone1], [Phone2], [Fax1], [Fax2], [CreateDate], [CreateUser], [MaintenanceDate], [MaintenanceUser], [LastOrderDate], [DeleteOnDate], [SyncStatus], [SyncDate], [SyncUser], [ERPID], [CreateCustomerID], [CreateContactID], [MaintenanceCustomerID], [MaintenanceContactID], [Active], [Deleted], [LockUser], [LockSessionID], [LockDate], [InUse], [AddressTypeVId], [CustomerVId])
select
coalesce([CustomerID], (select [CustomerID] from [dbo].[Customers] where [CustomerVId]=inserted.[CustomerVId])), coalesce([AddressTypeID], (select [AddressTypeID] from [dbo].[AddressTypes] where [AddressTypeVId]=inserted.[AddressTypeVId])), [CustomerAddressID], [AddressNameType], [Name], [ContactID], [Address1], [Address2], [Address3], [City], [County], [State], [Country], [Zip], [Phone1], [Phone2], [Fax1], [Fax2], [CreateDate], [CreateUser], [MaintenanceDate], [MaintenanceUser], [LastOrderDate], [DeleteOnDate], [SyncStatus], [SyncDate], [SyncUser], [ERPID], [CreateCustomerID], [CreateContactID], [MaintenanceCustomerID], [MaintenanceContactID], [Active], [Deleted], [LockUser], [LockSessionID], [LockDate], [InUse], coalesce([AddressTypeVId], (select [AddressTypeVId] from [dbo].[AddressTypes] where [AddressTypeID]=inserted.[AddressTypeID])), coalesce([CustomerVId], (select [CustomerVId] from [dbo].[Customers] where [CustomerID]=inserted.[CustomerID]))
from inserted
end
jtabuloc
  • 2,479
  • 2
  • 17
  • 33
  • I would recommend to use **`SCOPE_IDENTITY()`** instead of anything else (like `@@IDENTITY`) to grab the newly inserted identity value. [See this blog post for an explanation as to WHY](http://blog.sqlauthority.com/2007/03/25/sql-server-identity-vs-scope_identity-vs-ident_current-retrieve-last-inserted-identity-of-record/) – marc_s Aug 04 '15 at 06:30
  • @marc_s, I mentioned in my question that I can't use SCOPE_IDENTITY that is why I use @@IDENTITY. – jtabuloc Aug 04 '15 at 06:32
  • 1
    If you can elaborate on *why* `SCOPE_IDENTITY` isn't going to work, we may be able to offer other suggestions. E.g. an `OUTPUT` clause *may* be a better choice than using any of the standalone identity functions - but how can we know when you're just being coy about why you can't use the most obvious solution? – Damien_The_Unbeliever Aug 04 '15 at 06:49
  • I can't used SCOPE_IDENTITY because the existing table that I'm working on right now has that trigger that doesn't return identity even though I have SCOPE_IDENTITY in my query, so I shipped to use @@IDENTITY instead. Based on you answer does it mean that I have no choice but to alter that trigger to return identity instead of inventing idea just to get it? – jtabuloc Aug 04 '15 at 07:05

1 Answers1

1

Most of the important details are covered in the documentation:

@@IDENTITY and SCOPE_IDENTITY return the last identity value generated in any table in the current session. However, SCOPE_IDENTITY returns the value only within the current scope; @@IDENTITY is not limited to a specific scope.

There are also details about how @@IDENTITY might return unexpected values - but those are all related to (as might be expected), scope rather than other sessions.

The only function in this set that is concerning if you're dealing with other sessions is IDENT_CURRENT.

Of course, if your code is also dealing with triggers (that create nested scopes) that also manipulate tables with identity values (causing @@IDENTITY to return the "wrong" value) and you've already ruled out SCOPE_IDENTITY for some reason then you're out of luck.

Damien_The_Unbeliever
  • 234,701
  • 27
  • 340
  • 448
  • Hi, I'm aware of that documentation you posted. You mentioned about dealing with triggers, that is exactly my problem right now that is why I came up with this idea I posted to rule my problem. The existing table that I'm working on right now has that trigger that doesn't return identity even though I have SCOPE_IDENTITY in my query, so I shipped to use @@IDENTITY instead. Based on you answer does it mean that I have no choice but to alter that trigger to return @IDENTITY instead of inventing idea just to get identity? – jtabuloc Aug 04 '15 at 07:00
  • @JrTabuloc - no, if you're wanting to retrieve the identity value produced by a trigger, then you can use `@@IDENTITY` and that should work. – Damien_The_Unbeliever Aug 04 '15 at 07:10
  • The existing trigger doesn't return any identity. I posted the trigger in my question for clarity. May be I need to alter that trigger to return Identity. By the way, that trigger is already existing prior to my work that is why I'm afraid to change it because I'm not database expert to identify what would be the impact If I alter it to return Identity. – jtabuloc Aug 04 '15 at 07:19
  • @JrTabuloc - no, you shouldn't need to alter the trigger. `@@IDENTITY` can be used from outside the trigger to obtain the identity values. – Damien_The_Unbeliever Aug 04 '15 at 07:21
  • So are you telling to me that the second scenario I posted in my question will never occur? *The one that I'm afraid to happen* – jtabuloc Aug 04 '15 at 07:25
  • @JrTabuloc - no. Neither `SCOPE_IDENTITY` nor `@@IDENTITY` will ever return an identity value that applies to a different *session*. – Damien_The_Unbeliever Aug 04 '15 at 07:27
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/85075/discussion-between-jr-tabuloc-and-damien-the-unbeliever). – jtabuloc Aug 04 '15 at 07:32
  • Hi Damien, Sorry I misunderstood your last comment. "no. Neither SCOPE_IDENTITY nor @@IDENTITY will ever return an identity value that applies to a different session." Are you saying that this scenario will never happen? Transaction1 - 102 Transaction2 - 103 Transaction3 - 101 – jtabuloc Aug 04 '15 at 07:49