Updatable Views in SQL Server

Bandi
Posted by in Sql Server category on for Intermediate level | Points: 250 | Views : 1299 red flag

The one of most important usage of updatable view is to do two-way synchronisation between two databases in the same server.

Introduction

An Updatable VIEW is a view which is referencing only one table and having no calculations/expression/conversions as part of the SELECT query. When the View is changed through any DML operation, directly affects the underlying base table.


The below are the conditions applied to have an Updatable View:

  1.  View has to be built on only one single table
  2. There shouldn’t be any GROUP BY or HAVING clauses as part of SELECT statement
  3. Plain SELECT statement is allowed in Updatable View i.e. aggregate functions or any calculations or usage of set operators (UNIONINTERSECT or EXCEPT) are not allowed
  4. Any columns excluded from the VIEW must be NULL-able or have a DEFAULT clause in the base table, so that a whole row can be constructed for insertion


Problem

There are two databases (old and new) that are connected with two applications (old and new) respectively. Both databases are to be in sync in real-time. Any updates/inserts to any of the table in both databases should reflect the other database.

 

We can provide solution for this requirement by using triggers in both databases. i.e.

  1. One trigger in old database: It has to insert/update/delete the corresponding old table data to new table
  2. Other trigger in new database: It has to insert/update/delete the data to old database

There is a possibility to conflict/deadlock/data-loss by using the above two triggers in the case of concurrent users. To avoid this kind of issues, the better option is ‘Updatable View’.


Solution

By creating cross-database (means two separate databases in same instance/server) updatable views we can avoid trigger issues (such as concurrency, data-loss and tracing problems).

Whenever there is an entry or any updates to a table in either of the database, it should reflect both databases in real-time. Trigger causes data-loss for concurrent users of both (old and new) applications.

So the better option to avoid data-loss or exceptions for concurrent users is to make use of new DB’s table for both applications by the removal of old DB table. For implementing this, we should have old DB table in old database and new DB table in new database.

Now how do we have both tables and then redirecting old table DML operations to new DB table?

The answer is Updatable VIEW….

The following steps are required to redirect old DB table’s DML operations to new DB’s table:

  1. First rename old database table to something else. ( this step needs to be done after dumping old tables data to new DB table)
  2. Create Updatable View in Old DB by having same table name
  3. The view’s base table should be New DB’s table ( Ex: NewTable)
  4.  Alias the columns of new table to old table
  5. Take necessary actions if you have any not-null or null constraint mismatching between old and new table 
Suppose old DB table is OldTable then create view with the name OldTable

USE OldDB  -- create view in OLD DB
GO
CREATE VIEW OldTable -- the view name must be same as old table name
As 
SELECT Column1 as OldColumn1
	,Column2 as Col2
FROM NewDB.dbo.NewTable -- take table of New DB as underlying/base table

Here all DML operations against the view will affect base table (i.e. table in new database)

Note: you have to manage the both table property differences (such as Default constraints, identity columns, foreign keys and so on) if any.


Conclusion

Updatable Views are very useful to refer cross-databases in the same instance or other server's instance. Main purpose fo the view is to do any of the DML operation on underlying base table through virtual table(view).
Page copy protected against web site content infringement by Copyscape

About the Author

Bandi
Full Name: Chandrika Bandi
Member Level: Platinum
Member Status: Member,MVP
Member Since: 7/23/2013 5:52:37 AM
Country: India
[B]Mark This Response as Answer[/B] -- Chandu http://www.dotnetfunda.com/images/dnfmvp.gif
http://www.dotnetfunda.com

Login to vote for this post.

Comments or Responses

Login to post response

Comment using Facebook(Author doesn't get notification)