SQL Server select primary key from table where the key contains multiple columns

Question

I am working on a legacy database. I am not able to change the schema :( in a couple of tables the primary key uses multiple columns.

In the app I read the data in each row into a table the user then updates the data and I write the data back into the table.

Currently I concatenate the various PK columns and store them as a unique id for when I put the data back into the table.

Now I was wondering if there is a more efficient way to do that. Coming from a mySQL background I am not aware of any but thought SQL Server 2005 may have a function

SELECT PRIMARYKEY() as pk, ...  FROM table WHERE ...

the above would select the key that the database engine uses as the primary key for the given record

I searched and couldn't find anything. Its probably just me being fussy but I don't like the concatenation trick.

DC

Answer 1

"Currently I concatenate the various PK columns and store them as a unique id for when I put the data back into the table." Can't you just store the pk as two columns in the target table and use that to join back to the two columns on the source table?

What benefit is concatenating giving you here?

Answer 2

In SQL Server, there is no equivalent of PRIMARYKEY() that I would be aware of, really. You can consult the system catalog views to find out which columns make up the primary key, but you can't just simply select the primary key value(s) with a function call.

I would agree with StarShip3000 - what do you concatenate your PK values for? While I don't think a compound primary key made up of several columns is necessarily a very good idea, if it's a legacy system and you can't change it, I wouldn't bother concatenating the PK values on read, and then having to split them apart again when you write your data back. Just leave the structure as it is - compound keys aren't generally recommended, but they are indeed supported, no problem.