是否可以在每次调用视图时都保持不变的 SQL Server 视图中创建唯一 ID？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1842537/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Is it possible to create a Unique ID in an SQL Server View that will remain the same each time the view is called?
提问by Brendo
I've got 10 tables that I'm joining together to create a view. I'm only selecting the ID from each table, but in the view, each ID can show more than once, however the combination of all ID's will always be unique. Is there a way to create another column in this view that will be a unique ID?
我有 10 个表，我将它们连接在一起以创建一个视图。我只是从每个表中选择 ID，但在视图中，每个 ID 可以显示多次，但是所有 ID 的组合将始终是唯一的。有没有办法在此视图中创建另一列作为唯一 ID？
I'd like to be able to store the unique ID and use it to query against the view in order to get all the other ID's.
我希望能够存储唯一 ID 并使用它来查询视图以获取所有其他 ID。
回答by alistair
I had a similar issue where I needed to establish a hierarchy across multiple tables. If you are using an integer as the id in each of the tables, you could simply convert the ids of each table to a varchar and prefix them with a different letter for each table. For instance
我有一个类似的问题，我需要在多个表之间建立层次结构。如果您在每个表中使用整数作为 id，您可以简单地将每个表的 id 转换为 varchar，并为每个表使用不同的字母作为前缀。例如
CREATE VIEW LocationHierarchy as
CREATE VIEW LocationHierarchy as
SELECT 'C' + CONVERT(VARCHAR,[Id]) as Id
,[Name]
,'S' + CONVERT(VARCHAR,[State]) as parent
FROM [City]
UNION
SELECT 'S' + CONVERT(VARCHAR,[Id]) as Id
,[Name]
,'C' + CONVERT(VARCHAR,[Suburb]) as parent
FROM [Suburb]
SELECT 'C' + CONVERT(VARCHAR,[Id]) as Id
,[Name]
,'S' + CONVERT(VARCHAR,[State]) as parent
FROM [City]
UNION
SELECT 'S' + CONVERT(VARCHAR,[Id]) as Id
,[Name]
,'C' + CONVERT(VARCHAR,[Suburb]) as parent
FROM [Suburb]
etc
等等
The effectiveness of this solution will depend on how large your dataset is.
此解决方案的有效性将取决于您的数据集有多大。
回答by DEEPA
Yes, recently I have had the same requirement.
是的，最近我也有同样的要求。
When creating the view, keep the select statement as a TEMP_TABLEand then use Row_number()function on this TEMP_TABLE.
创建视图时，将 select 语句保留为 a TEMP_TABLE，然后Row_number()在 this 上使用函数TEMP_TABLE。
Here's an example:
下面是一个例子：
CREATE VIEW VIEW_NM
AS
SELECT Row_number() OVER(ORDER BY column_nm DESC) AS 'RowNumber' FROM
(SELECT COL1,COL2 FROM TABLE1
UNION
SELECT COL1,COL2 FROM TABLE2
) AS TEMP_TABLE;
回答by Andomar
I think you can do that using ROW_NUMBER(), at least if you can guarantee an ordering of the view. For example:
我认为你可以使用 ROW_NUMBER() 来做到这一点，至少如果你能保证视图的顺序。例如：
SELECT
ROW_NUMBER() OVER (ORDER BY col1, col2, col3) as UniqueId
FROM <lotsa joins>
As long as the order stays the same, and only fields are added at the end, the id will be unique.
只要顺序不变，只在最后添加字段，id就是唯一的。
回答by JonH
No you cannot do that in a view, what you can do is create a temporary table to hold all this information and create a key or unique id there for each row.
不，您不能在视图中这样做，您可以做的是创建一个临时表来保存所有这些信息，并在那里为每一行创建一个键或唯一 ID。
回答by Tom H
The fact that you're attempting to do this points to much bigger problems in the database design and/or application architecture.
您尝试这样做的事实表明数据库设计和/或应用程序架构中存在更大的问题。
Since you have 10 tables and I'm going to guess that the DB designer(s) just slapped on ID INT IDENTITY to all of the tables you will end up having about (2^31)^10 possible rows in the table.
由于您有 10 个表，我猜数据库设计者只是对所有表设置了 ID INT IDENTITY，您最终将在表中拥有大约 (2^31)^10 行。
The only data type that I can think of that might cover that number would be to translate all of the integers into 0-padded strings and put them all together as a big CHAR.
我能想到的可能涵盖该数字的唯一数据类型是将所有整数转换为 0 填充字符串，并将它们全部放在一起作为一个大 CHAR。
My guess though is that your real problem isn't getting this ID for a view but some other thing that you're attempting to do, which is the question that you should be asking. Just a hunch though.
我的猜测是，您真正的问题不是为视图获取此 ID，而是您尝试做的其他一些事情，这是您应该问的问题。不过只是一种预感。
回答by RickNZ
One possibility would be to call a function from your view that calculates a hash code on all of the ID columns. If you use a decent cryptographic hash algorithm, the odds of a collision are minuscule (probably more likely that the disk would deliver bad data). Even easier, you could of course just concatenate the different IDs into a single, much larger ID, perhaps as a binary or varbinary column.
一种可能性是从您的视图中调用一个函数来计算所有 ID 列的哈希码。如果您使用合适的加密散列算法，则发生冲突的几率很小（可能更可能是磁盘传送错误数据）。更简单的是，您当然可以将不同的 ID 连接成一个更大的 ID，可能作为一个 binary 或 varbinary 列。
Storing that ID and being able to query against it would be a bit more work. I can't think of a way to both compute it and store it from a view. You would probably need a stored procedure to create it first; the details depend heavily on the specifics of your app.
存储该 ID 并能够对其进行查询需要更多的工作。我想不出一种方法来计算它并从视图中存储它。您可能首先需要一个存储过程来创建它；细节在很大程度上取决于您的应用程序的细节。
