Is it possible to create a Unique ID in an SQL Server View that will remain the same each time the view is called?

Question

I've got 10 tables that I'm joining together to create a view. I'm only selecting the ID from each table, but in the view, each ID can show more than once, however the combination of all ID's will always be unique. Is there a way to create another column in this view that will be a unique ID?

I'd like to be able to store the unique ID and use it to query against the view in order to get all the other ID's.

Answer 1

I had a similar issue where I needed to establish a hierarchy across multiple tables. If you are using an integer as the id in each of the tables, you could simply convert the ids of each table to a varchar and prefix them with a different letter for each table. For instance

CREATE VIEW LocationHierarchy as

SELECT 'C' + CONVERT(VARCHAR,[Id]) as Id
      ,[Name]
      ,'S' + CONVERT(VARCHAR,[State]) as parent
  FROM [City]
  UNION
  SELECT 'S' + CONVERT(VARCHAR,[Id]) as Id
      ,[Name]
      ,'C' + CONVERT(VARCHAR,[Suburb]) as parent
  FROM [Suburb]

etc

The effectiveness of this solution will depend on how large your dataset is.

Answer 2

I think you can do that using ROW_NUMBER(), at least if you can guarantee an ordering of the view. For example:

 SELECT
     ROW_NUMBER() OVER (ORDER BY col1, col2, col3) as UniqueId
 FROM <lotsa joins>

As long as the order stays the same, and only fields are added at the end, the id will be unique.