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

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?

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

StackExchange Database Administrators Q#305975, answer score: 7