Explain how to make views updatable in SQL Server 2000

Explain how to make views updatable in SQL Server 2000[Hint - using Instead of Triggers and Partitioned views].

INSTEAD OF Triggers: These can be created on a view to make a view updateable. The INSTEAD OF trigger is executed instead of the data modification statement on which trigger is defined. This trigger enables the user to provide set of actions that must take place to process data modification statement. Partitioned views: It’s a view defined by a UNION ALL of member tables structured in same way but stored separately as multiple tables in either same SQL Server or multiples ones (federated database servers).

Explain how to make views updatable in SQL Server 2000[Hint - using Instead of Triggers and Partitioned views].

INSTEAD OF Triggers: Cause the trigger to fire instead of executing the triggering event or action. It prevents unnecessary changes to be made.

Example: Causes the trigger to fire instead of the update (action)
CREATE TRIGGER Employee_update ON Employee INSTEAD OF
UPDATE AS

{
TRIGGER Definition
}
Partitioned view:
Partitioned view joins the horizontally portioned data. This data may belong to one ore more servers. It makes the data appear as one table. A portioned view can either be local or distributed. A local portioned view resides on the same instance of the SQL server while the distributed may reside on a different server.

Syntax:
The view is then created by UNIONing all the tables and an updateable partitioned View results
Server 1 :
CREATE TABLE Customer1
(CustomerID INTEGER PRIMARY KEY
CHECK (CustomerID BETWEEN 1 AND 32999),
... -- Additional column definitions)
Similar tables created for Server 2 and 3
Partitioned view for server 1
CREATE VIEW Customers AS
SELECT * FROM CompanyDatabase.TableOwner.Customer1
UNION ALL
SELECT * FROM Server2.CompanyDatabase.TableOwner.Customer2
UNION ALL
SELECT * FROM Server3.CompanyDatabase.TableOwner.Customer3
Query optimization tips
Use indexes. Specify only the columns necessary while using select query.......
Backup devices offered by SQL Server 2000
Disk devices: These are files that are stored on the hard disk or some other storage media. They can be of a variable size........
Database backup methods
Full Backups Differential Backups Transaction Log Architecture Backups.......
Post your comment