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?

Answer 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

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

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

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

Answer 5

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

Answer 6

I realize it's been stated that the id doesn't have to be stable, but I don't see an answer providing a 'stable' version so I felt it was warranted.

A stable way to generate an id for a view is to create a SHA1 hash, whether it use a column (or columns) from within the view, or a runtime computation.

Generally, this is ideal to use since you can reliably come back to this ID over and over again. In some cases, it can even be used to know whether or not something has changed that's important for the business logic.

For example, If you create a hash by concatenating the start & end dates CONCAT_WS('-', ...[<your_columns>]) of some appointment, save that hash elsewhere, then come back to query it and realize it no longer exists, then you know that there's been 1 (or more) updates since you've last queried this appointment.

To do this in SQL Server, you can use some built-in functions:

SELECT 
  HASHBYTES('SHA1', CAST(some_table.some_column as VARCHAR)) as id,
FROM 
  your_table

Though, since this function returns a varbinary(1), you may want to cast this to a proper string.

Here's how you would do that:

SELECT 
  master.dbo.fn_varbintohexstr(HASHBYTES('SHA1', CAST(some_table.some_column as VARCHAR))) as id,
FROM 
  your_table

Bear in mind, the master.dbo portion is assuming some SQL Server naming conventions

And lastly, you'll notice that SQL Server infers the data type for this column as nvarchar(max), so if you want to reel this in, here' how you can do that:

SELECT 
  CAST(master.dbo.fn_varbintohexstr(HASHBYTES('SHA1', CAST(some_table.some_column as VARCHAR))) as varchar(42)) as id,
FROM 
  your_table

Note: This answer is known to work on SQL Server 2019 and up. Not to say it doesn't work on older versions, I just didn't check.