Column property of updatable view is incorrect when inner join is used

Question

I have a table atbl_UserSetting with a column with name ID which is a primary key with identity and a seed/increment.

This table is accessible through a view for regular users. This is the view:

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

Now when I run this query, it will return true (1):

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

Same for INFORMATION_SCHEMA.COLUMNS.

However, if I change the view to this instead (joining in the User-table):

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

The same property of the column returns false (0).

This in turn leads to SqlCommandBuilder (.NET System.Data.SqlClient) generating insert-command with the ID column and when calling Update on SqlAdapter it will fail since the IDENTITY_INSERT is set to OFF. When the view use the WHERE clause instead of joining in another table, the columnproperty returns true(1) for IsIdentity and SqlCommandBuilder correctly does not include the ID column in the insert command.

So my question is, why is not SQL Server able to recognize correct value of the column property "IsIdentity" for a view when other tables are joined in?

Answer 1

The IsIdentity property indicates whether the column is considered to have the identity property or not.

When you test the column of a view, you are asking the engine to determine whether the identity property will transfer to that column in the view. The question is not whether the underlying column has that property or not (which is what the framework you are using seems to expect).

The behaviour is not terribly well documented (or indeed consistent in all cases), but it is at least referred to in SELECT - INTO Clause (Transact-SQL):

When an existing identity column is selected into a new table, the new column inherits the IDENTITY property, unless one of the following conditions is true:

• The SELECT statement contains a join.
• Multiple SELECT statements are joined by using UNION.
• The identity column is listed more than one time in the select list.
• The identity column is part of an expression.
• The identity column is from a remote data source.

You are not selecting an identity column into a new table, but the principle is the same. The view is a derived table-valued expression, so the server follows its (quirky) rules for the inheritance of the identity property.

To be clear: Every column or expression must have a fully-specified type. The identity property is one aspect of that type. Depending on the query construction (in a view or elsewhere), the identity property may be preserved or lost. The documentation mentioned above illustrates some cases, but it is not exhaustive.

I have no idea about SqlBuilder, but its apparent expectation about IsIdentity is not unreasonable—it's just not reflective of how SQL Server works (and has always done).

A very simple example where the identity property does not transfer because the column is listed more than once:

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');

TableIdentity	ViewIdentity
1	0

Online db<>fiddle demo

Answer 2

It seems that this is only a problem with column property value returned by SQL Server system views/functions e.g. sys.all_columns.

How it determines this property is not obvious, at least to me. I do not think that it has anything to do with row/key uniqueness as the following view also has "Identity" = 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;

Maybe it expects no joins?

Another curious detail: sys.identity_columns returns NULL for seed_value, increment_value, last_value columns for all updatable views.

If you actually run INSERT INTO atbv_UserSetting in SSMS it will succeed for all versions of your view regardless of the actual "Identity" property value.

FYI: SSMS uses sys.all_columns view to get the Identity flag.

Answer 3

The second query is not semantically the same as the first query since it can return more than one row per ID in the dbo.atbl_UserSetting. I.e., apparently SQL Server determines that it is safe to assume the identity attribute when the outer query only involves one table (the second query is only used in the WHERE clause meaning it is impossible to "add rows" to the result).