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):
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?
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