在SQL Server 2008中为视图添加身份列

Question

这是我的视图:

Create View [MyView] as
(
Select col1, col2, col3 From Table1
UnionAll
Select col1, col2, col3 From Table2
)

我需要添加一个名为Id的新列，并且需要使这个列是唯一的，因此我考虑将新列添加为标识列。我必须提到，这个视图返回了大量的数据，所以我需要一种性能良好的方法。此外，我使用了两个带有union all的select查询，我认为这可能有点复杂，您有什么建议？

- Saeid

---

在SQL Server中，视图只是一个“存储的查询” - 它在数据库中没有任何物理表示。因此，您不能向视图添加标识列。 - marc_s

id不稳定是否可以接受（如果Table1增长，Table2的第一个id将与以前不同）？ - Oded

@marc_s - 可能OP只是在寻找此处的ROW_NUMBER类型功能。 - Oded

@marc_s - 确实如此，这就是为什么我问稳定的ID是否很重要的原因... - Oded

如果您想要一个“稳定”的ID，也就是说，对于视图中的每一行，您始终都会有相同的ID，那么您必须将ID与持有该行的表相关联并存储在某个地方。视图不是您可以存储此类信息的地方，因此您需要一个表或者需要向用于构建视图的表添加列。 - Mikael Eriksson

Answer 1

在 SQL Server 2008 中使用 ROW_NUMBER() 函数。

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

- Daniel P

---

是的，但这不会提供稳定的ID。 - Oded

如果您发布代码、XML或数据示例，请在文本编辑器中突出显示这些行，并单击编辑器工具栏上的“代码示例”按钮（{}）以使其格式化和语法高亮！ - marc_s

谢谢您。在提交表格之前，在视图中测试数据确实非常有用。在我的情况下，更方便的方法是只使用通配符，如下所示： SELECT ROW_NUMBER() OVER (ORDER BY col1) as ID, x.* FROM (SELECT blah blah blah) as x - cowsay

Answer 2

视图只是一个存储的查询，不包含数据本身，因此您可以添加一个稳定的ID。如果您需要用于其他目的（例如分页）的ID，可以执行以下操作：

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
)

- Diego

---

是的，但这不会提供稳定的ID。 - Oded

是的，但他没有说他需要一个。我猜他需要这个ID来进行其他操作，比如分页。 - Diego

Answer 3

使用ROW_NUMBER()查询返回的行不保证每次执行时都按照完全相同的顺序排序，除非满足以下条件：

1. 分区列的值是唯一的。[分区是父子关系，例如老板有3名员工] [忽略]
2. ORDER BY列的值是唯一的。[如果第1列是唯一的，则row_number应该是稳定的]
3. 分区列和ORDER BY列的值组合是唯一的。[如果您需要在ORDER BY中使用10列以获得唯一性...可以这样做以使row_number稳定]"

这里还有一个次要问题，这是一个视图。Order By在视图中并不总是有效（长期以来的sql错误）。暂时忽略row_number()：

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
)

- Rich

---

这个问题并没有涉及到row_number。这是对另一个回答或评论的补充吗？否则，这个回答缺少一些上下文/前提条件。它似乎不是这个问题的答案。 - Kissaki

Answer 4

使用ROW_NUMBER()与"order by (select null)"，这样会更加高效并得到你想要的结果。

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

- Ahmed Ramzy

---

ROW_NUMBER() 不会产生稳定的 ID。一旦基础数据发生变化，ID 可能会变得不一致。 - Kissaki

在我的情况下，这已经足够好了，因为我实际上并不需要索引，但是NHibernate强制我必须有一个Id列来映射视图。 - undefined

Answer 5

使用 "row_number() over ( order by col1) as ID" 很昂贵。以下方法成本更低:

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
)

- klingu

---

这个问题没有提到row_number。这是针对其他内容的评论吗？ - Kissaki

生成GUID也不是免费的。因此，它取决于数据和排序的成本。 - Kissaki

Answer 6

我知道有人说过id不需要稳定，但是我没有看到提供一个“稳定”版本的答案，所以我觉得这是必要的。

为视图生成一个稳定的id的方法是创建一个SHA1哈希值，可以使用视图内的列（或多个列），也可以在运行时计算。

通常情况下，这是理想的方法，因为您可以可靠地反复使用这个ID。在某些情况下，它甚至可以用于判断业务逻辑中是否发生了重要的更改。

例如， 如果您通过连接开始和结束日期CONCAT_WS('-', ...[])来创建一个哈希值，并将该哈希值保存在其他地方，然后再次查询它并发现它不存在了，那么您就知道自从上次查询这个约会以来已经进行了1次（或多次）更新。

在SQL Server中，您可以使用一些内置函数来实现这一点：

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

虽然这个函数返回的是varbinary(1)，但你可能想将其转换为正确的字符串。

以下是如何进行转换的方法：

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

记住，master.dbo 部分假设了一些 SQL Server 命名约定。

最后，你会注意到 SQL Server 推断此列的数据类型为 nvarchar(max)，所以如果你想限制它的大小，可以按照以下方式进行操作：

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

注意：此答案已知适用于SQL Server 2019及更高版本。并不是说它不能在旧版本上运行，只是我没有进行过检查。

- jhavenz