Add Identity column to a view in SQL Server 2008

问题:

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?

回答1:

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

回答2:

The view is just a stored query that does not contain the data itself so you can add a stable ID. If you need an id for other purposes like paging for example, you can do something like this:

create view MyView as 
(
    select row_number() over ( order by col1) as ID, col1 from  (
        Select col1 From Table1
        Union All
        Select col1 From Table2
    ) a
)

回答3:

There is no guarantee that the rows returned by a query using ROW_NUMBER() will be ordered exactly the same with each execution unless the following conditions are true:

1. Values of the partitioned column are unique. [partitions are parent-child, like a boss has 3 employees][ignore]
2. Values of the ORDER BY columns are unique. [if column 1 is unique, row_number should be stable]
3. Combinations of values of the partition column and ORDER BY columns are unique. [if you need 10 columns in your order by to get unique... go for it to make row_number stable]"

There is a secondary issue here, with this being a view. Order By's don't always work in views (long-time sql bug). Ignoring the row_number() for a second:

create view MyView as 
(
    select top 10000000 [or top 99.9999999 Percent] col1 
    from  (
        Select col1 From Table1
        Union All
        Select col1 From Table2
    ) a order by col1
)

回答4:

Using "row_number() over ( order by col1) as ID" is very expensive. This way is much more efficient in cost:

Create View [MyView] as
(
    Select ID = isnull(cast(newid() as varchar(40)), '')
           , col1
           , col2
           , col3 
    From Table1
    UnionAll
    Select ID = isnull(cast(newid() as varchar(40)), '')
           , col1
           , col2
           , col3 
    From Table2
)