Add Identity column to a view in SQL Server 2008

sql, sql-server, sql-server-2008, t-sql, view

Solution

Use the `ROW_NUMBER()` function in SQL Server 2008.

Create View [MyView] as

SELECT ROW_NUMBER() OVER( ORDER BY col1 ) AS id, col1, col2, col3
FROM(
    Select col1, col2, col3 From Table1
    Union All
    Select col1, col2, col3 From Table2 ) AS MyResults
GO

Problem

This is my view: ``` Create View [MyView] as ( Select col1, col2, col3 From Table1 UnionAll Select col1, col2, col3 From Table2 ) ``` I need to add a new column named `Id` and I need to this column be unique so I think to add new column as identity. I must mention this view returned a large of data so I need a way with good performance, And also I use two select query with union all I think this might be some complicated so what is your suggestion?

Original source