Is there a way to only return a PART of a compound primary key?

Question

I have a SQL table with a compound key, ID (int, autogenerated) and TaxpayerID (varchar, user enters it). When the user registers, they enter a taxpayer ID & the taxpayer ID combined with the ID make a Primary Key.

Normally after entering a record, I'd return used @@Identity and return it to a Integer variable in my .NET application. However, it seems to be failing, I think because it is a compound key.

Is there a way to only return a PART of a compound primary key? (in this case, just the ID field)

Answer 1

Suggest return SCOPE_IDENTITY(). That will return the last IDENTITY value created in your scope. I'd suggest to prefer SCOPE_IDENTITY() because

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

In your case, it's a bit muddled with the compound, but if you're looking for the auto-incremented IDENTITY field, this will do it for you. The PK on the table doesn't matter. If there's a field marked with IDENTITY, and you want it returned, use SCOPE_IDENTITY() immediately after the INSERT.