使用內連接時可更新視圖的列屬性不正確

我有一個表 atbl_UserSetting，其中有一列名稱為 ID，它是具有標識和種子/增量的主鍵。

普通使用者可以通過視圖訪問此表。這是視圖：

CREATE OR ALTER   VIEW [dbo].[atbv_UserSetting]
AS
SELECT        ID, User_ID, [Key], Value
FROM            dbo.atbl_UserSetting AS US
WHERE        EXISTS
   (SELECT        TOP (1) 1 AS Expr1
   FROM            dbo.atbl_User AS U
   WHERE        (ID = US.User_ID) AND (Login = SUSER_NAME()))

現在，當我執行此查詢時，它將返回 true (1)：

SELECT COLUMNPROPERTY(OBJECT_ID('atbv_UserSetting'),'ID','IsIdentity')

INFORMATION_SCHEMA.COLUMNS 也一樣。

但是，如果我將視圖更改為此（加入使用者表）：

CREATE OR ALTER   VIEW [dbo].[atbv_UserSetting]
AS
SELECT        US.ID, US.User_ID, US.[Key], US.Value
FROM            dbo.atbl_UserSetting AS US
INNER JOIN dbo.atbl_User AS U ON U.ID = US.User_ID
WHERE U.[Login] = SUSER_NAME()

列的相同屬性返回 false (0)。

這反過來會導致 SqlCommandBuilder (.NET System.Data.SqlClient) 生成帶有 ID 列的插入命令，並且在 SqlAdapter 上呼叫 Update 時它將失敗，因為 IDENTITY_INSERT 設置為 OFF。當視圖使用 WHERE 子句而不是加入另一個表時，列屬性為 IsIdentity 返回 true(1)，並且 SqlCommandBuilder 正確地不包括插入命令中的 ID 列。

所以我的問題是，當其他表加入時，為什麼 SQL Server 不能辨識視圖的列屬性“IsIdentity”的正確值？

該IsIdentity屬性指示該列是否被認為具有標識屬性。

當您測試視圖的列時，您要求引擎確定身份屬性是否將轉移到視圖中的該列。問題不在於底層列是否具有該屬性（這似乎是您使用的框架所期望的）。

該行為沒有得到很好的記錄（或在所有情況下確實一致），但至少在SELECT - INTO Clause (Transact-SQL)中提到了它：

將現有標識列選擇到新表中時，新列將繼承 IDENTITY 屬性，除非以下條件之一為真：

• SELECT 語句包含一個連接。
• 使用 UNION 連接多個 SELECT 語句。
• 標識列在選擇列表中多次列出。
• 標識列是表達式的一部分。
• 標識列來自遠端數據源。

您不是在新表中選擇標識列，但原理是相同的。視圖是派生的表值表達式，因此伺服器遵循其（古怪的）規則來繼承身份屬性。

需要明確的是：每個列或表達式都必須具有完全指定的類型。身份屬性是該類型的一個方面。根據查詢構造（在視圖中或其他地方），可能會保留或失去身份屬性。上面提到的文件說明了一些情況，但並不詳盡。

我不知道SqlBuilder，但它對它的明顯期望IsIdentity並不是不合理的——它只是不能反映 SQL Server 的工作方式（並且一直如此）。

一個非常簡單的範例，其中標識屬性不轉移，因為列被列出了不止一次：

CREATE TABLE dbo.T1 (i integer IDENTITY NOT NULL);
GO
CREATE OR ALTER VIEW dbo.V
WITH SCHEMABINDING AS
SELECT T1.i, T1.i AS i2
FROM dbo.T1 AS T1;
GO
SELECT TableIdentity = COLUMNPROPERTY(OBJECT_ID(N'dbo.T1', 'U'), N'i', 'IsIdentity');
SELECT ViewIdentity = COLUMNPROPERTY(OBJECT_ID(N'dbo.V', 'V'), N'i', 'IsIdentity');

線上db<>fiddle 展示

似乎這只是 SQL Server 系統視圖/函式返回的列屬性值的問題，例如sys.all_columns。

它如何確定這個屬性並不明顯，至少對我來說是這樣。我認為它與行/鍵唯一性沒有任何關係，因為以下視圖也具有“身份”= 0：

ALTER VIEW TestView2
AS
   SELECT a.ID, a.Col1 FROM dbo.TestTbl AS a
       -- Should only ever return 0 or 1 rows from TestTbl2 for each row in TestTbl
       CROSS APPLY ( SELECT TOP 1 Col1 FROM dbo.TestTbl2 AS b WHERE a.Col1 = b.Col1 ORDER BY ID ) AS c;

也許它不希望加入？

另一個奇怪的細節：sys.identity_columns返回所有可更新視圖NULL的seed_value, increment_value,last_value列。

如果您實際INSERT INTO atbv_UserSetting在 SSMS 中執行，則無論實際的“身份”屬性值如何，它都會對您的所有視圖版本成功。

僅供參考：SSMS 使用 sys.all_columns 視圖來獲取標識標誌。

引用自：https://dba.stackexchange.com/questions/305975