Relational database management systems (RDBMS) rely on Structured Query Language (SQL) as their foundation for data management and manipulation. Views in SQL Server offer a strong way to abstract the underlying intricacy of data structures. While many views are read-only, updatable views open a door to dynamic data manipulation through INSERT, UPDATE, and DELETE operations. We will examine the idea of updatable views in SQL Server, examine their operation, and offer a useful example in this tutorial.

Understanding Updatable Views

An updatable view in SQL Server is a view that allows modifications such as INSERT, UPDATE, or DELETE operations on the underlying tables through the view. To make a view updatable, it must meet the below criteria.

• Single Table Rule: Any operation on the view will update only a single base table at a time.  and it cannot update the data in derived columns.
• No DISTINCT, GROUP BY, or HAVING: The SELECT statement used to create the view should not include DISTINCT, GROUP BY, or HAVING clauses.
• No Aggregate Functions: Avoid using aggregate functions (like SUM, AVG, COUNT) in the SELECT statement of the view.
• No TOP Clause: The SELECT statement shouldn't include the TOP keyword.
• No COMPUTE or COMPUTE BY: These are not allowed in the SELECT statement of the view.
• No OUTER JOIN: Views with OUTER JOINs are usually not updatable.

Let's Understand this with an example. Let's consider a scenario where we have a database with a table, Person_Data, and a view, Data_view.

CREATE TABLE  Person_Data
(
   [No]   int Identity primary key, 
   [Name]   varchar(40),
   [Last_name]   varchar(20),
   [age]   bigint,
)
GO

--Here is our view to the table

Create VIEW [Data_view]    As SELECT [Name],[no],[Last_name] FROM Person_Data
GO

The view ‘Data_view’ meets the criteria for updatable views. It's based on a single table ‘Person_Data ’ and the SELECT statement doesn't involve DISTINCT, GROUP BY, or aggregate functions.

Let's add data to the Table.

--As an example insert few entries
INSERT INTO Person_Data VALUES ( 'abc','iop',32 )
INSERT INTO Person_Data VALUES ( 'qwe','jkl',35)
INSERT INTO Person_Data VALUES ( 'asd','fgh',38 )
INSERT INTO Person_Data VALUES ( 'zxc','dfg',14 )


--Check if everything works fine
SELECT * FROM Person_Data 
SELECT * FROM Data_view
GO

We can see data in view as well as in the table.

Now let's do some operations on the view, just like we would do on a regular table.

UPDATE Data_view SET [Name] = 'bnm', [Last_name] = 'ert' where [No] = '1'
INSERT INTO Data_view ([Name], [Last_name])VALUES ('ert', 'yui');   
DELETE Data_view where [No] = '2'
GO


--See the data in view and in table should be same
SELECT * FROM Person_Data 
SELECT * FROM Data_view

These update/delete operations are transparently applied to the underlying 'Person_Data' table due to the updatable nature of the view.

Limitations and Considerations

While updatable views provide flexibility, they come with limitations:

Complexity: Updatable views are restricted in terms of complexity. If your data model involves complex relationships, you might need to resort to stored procedures or direct table manipulation.

Performance: Depending on the complexity of the view, performance might be impacted. Always test and profile your queries.

Security: Cautious about granting update permissions on views, ensuring that users can only modify data that aligns with business rules.

Conclusion

Updatable views in SQL Server offer a convenient and efficient way to manage data through a simplified interface. When designed and used correctly, they provide a valuable layer of abstraction for users.