SQLServerCentral Article

Unlocking Power with Updatable Views in SQL Server


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.

   [No]   int Identity primary key, 
   [Name]   varchar(40),
   [Last_name]   varchar(20),
   [age]   bigint,
--Here is our view to the table
Create VIEW [Data_view]    As SELECT [Name],[no],[Last_name] FROM Person_Data

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

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'

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


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.


4.25 (4)

You rated this post out of 5. Change rating




4.25 (4)

You rated this post out of 5. Change rating