5

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

enter image description here

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?

4
  • Note you may want to link to or provide more details on which SqlBuilder you're using (since that seems to be a common name for multiple different technologies out there). Hopefully you're lucky enough that someone on here has encountered the same issue before and can provide a workaround. But at least Paul was able to provide the reason why this happens. I've ran into similar kind of issues using Microsoft's Entity Framework, EF Core specifically, and it was a matter of configuring the settings a little differently to workaround some of the edge case issues.
    – J.D.
    Jan 12, 2022 at 12:49
  • 2
    Side point: TOP (1) is completely superfluous in an EXISTS, as is the column alias, or for that matter anything in the SELECT. All will be completely elided by the compiler Jan 12, 2022 at 13:43
  • @Charlieface Ok, thanks. Yes it was SSMS that added an alias for the column. So it would be better to just write (in the where exists): SELECT 1 FROM etc... ?
    – jmelhus
    Jan 12, 2022 at 15:16
  • Yes correct, that's the pretty standard way to write it Jan 12, 2022 at 15:47

3 Answers 3

7

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

0
3

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.

1

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

1
  • Agree, they are semantically not the same. So the argument is that the ID column of the UserSettings table could not be used to identify the row anymore when we are joining in another table which potentially could add rows to the result set?
    – jmelhus
    Jan 12, 2022 at 9:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.