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:
- View has to be built on
only one single table
- There shouldn’t be any
GROUP BY or HAVING clauses as part of SELECT statement
- Plain SELECT statement is
allowed in Updatable View i.e. aggregate functions or any calculations or usage
of set operators (UNION, INTERSECT or EXCEPT) are not allowed
- 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.- One trigger in old database: It has to insert/update/delete
the corresponding old table data to new
table
- 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:
- First rename old database table to something
else. ( this step needs to be done after dumping old tables data to new DB
table)
- Create Updatable View in Old DB by having
same table name
- The view’s base table should be New DB’s
table ( Ex: NewTable)
- Alias the columns of new table to old table
- 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).