SQL Server Interview Question #103

Can data be inserted, updated, or deleted through a view?

Views, Stored Procedures & User-Defined Functions Mid-Level Intermediate

Quick Interview Answer

Some views are directly updatable, but not all views are. A simple view based on a single table may allow INSERT, UPDATE, or DELETE when SQL Server can unambiguously map the modification to the underlying table. Views containing certain constructs—such as aggregates, GROUP BY, DISTINCT, UNION, or calculations—may not be directly updatable. INSTEAD OF triggers can sometimes be used to define custom modification behavior, but they add complexity.

Detailed Explanation

Some views are directly updatable, but not all views are. A simple view based on a single table may allow INSERT, UPDATE, or DELETE when SQL Server can unambiguously map the modification to the underlying table.

Views containing certain constructs—such as aggregates, GROUP BY, DISTINCT, UNION, or calculations—may not be directly updatable. INSTEAD OF triggers can sometimes be used to define custom modification behavior, but they add complexity.

Code Example

CREATE VIEW dbo.vw_ProductNames
AS
SELECT Id, Name
FROM dbo.Products;
GO

UPDATE dbo.vw_ProductNames
SET Name = N'Updated Product'
WHERE Id = 10;