unique identity or identifier for view in sql server 2005

Question

I've got a view that's a union of two tables that have overlapping keys and I want to uniquely identify the rows for later retrieval. How can I add an identity or identifier column to the view rows so I can retrieve the rows later by that value?

Answer 1

Hard to answer without your table definitions to hand. However, could you not create an artificial key on the view e.g:

SELECT 'TABLE1' + CAST(KeyColumn AS VARCHAR) AS 'Key' FROM TABLE1

UNION

SELECT 'TABLE2' + CAST(KeyColumn AS VARCHAR) AS 'Key' FROM TABLE2

Answer 2

I wound up using a uniqueIdentifier field defaulted to NewID() and then populated the archive and current tables with GUIDs.