Add Identity column to a view in SQL Server 2008

Question

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?

Solution 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

Solution 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
)

Solution 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
)

Solution 4

use ROW_NUMBER() with "order by (select null)" this will be less expensive and will get your result.

Create View [MyView] as
SELECT ROW_NUMBER() over (order by (select null)) as id, *
FROM(
    Select col1, col2, col3 From Table1
    Union All
    Select col1, col2, col3 From Table2 ) R
GO

Solution 5

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
)

View more solutions

Comments

[marc_s]

A view in SQL Server is just a "stored query" - it doesn't have any physical representation in the database. Therefore, you cannot add an identity column to a view

[Oded]

Is it OK that the id will not be stable (if Table1 grows, the first id of Table2 will be different from before)?

[Oded]

@marc_s - It is possible that the OP is just looking for ROW_NUMBER type functionality here.

[marc_s]

@Oded: most likely, yes. That should work - but it's not an identity column, really ...

[Oded]

@marc_s - True, which is why I asked if a stable ID is important...

[Saeid]

@Oded I think the stability is important here. I use a temp Table to get Ids from this view and support pagedIndexList.

[Oded]

@Saeid - You think? This is important. You should know.

[Mikael Eriksson]

If you want an id that is "stable" meaning that for each row in the view you always will have the same id you have to store the ID somewhere with a relation to the table holding the row. A view is not a place where you can store such info so you need a table or you need to add a column to the tables that you use to build the view.

[Oded]

Yes, but this will not provide a stable id.

[Oded]

Yes, but this will not provide a stable id.

[marc_s]

If you post code, XML or data samples, PLEASE highlight those lines in the text editor and click on the "code samples" button ( { } ) on the editor toolbar to nicely format and syntax highlight it!

[Diego]

true, but he did not say he needs one. I assume he needs the id for some other purposes like paging for example

[cowsay]

Thank you for this. This is really useful for testing data in a view before committing a table. In my case, it's more conveient to just use a wildcard like so: SELECT ROW_NUMBER() OVER (ORDER BY col1) as ID, x.* FROM (SELECT blah blah blah) as x

[Kissaki]

The question does not talk about row_number. Is this supposed to be a comment to another answer or comment? Otherwise, this answer is missing some context/premise. It does not seem to be an answer to the question.

[Kissaki]

The question does not talk about row_number. Is this supposed to be a comment to something else?

[Kissaki]

Generating GUIDs is not free either. So it depends on the data and how expensive the ordering is.

[Kissaki]

ROW_NUMBER() does not produce a stable ID. As soon as the underlying data changes the IDs may become inconsistent.