Thursday, May 3, 2012

SQL: Views creation and Restoration

A view is basically a virtual table which does not exist physically and contains columns and data from different tables.  A view itself does not contain any data or information; it is a set of queries that are applied to one or more tables. The data accessible through a view is not stored in the database as a distinct objects rather it is stored as a SELECT statement. The result set of the SELECT statement forms the virtual table returned by the view. A user can use this virtual table by referencing the view name in Transact-SQL statements the same way a table is referenced.

Creating a View
Below is the syntax for creating view

CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition

SELECT EmpID, EmpName FROM EmpData
It will create a View with the name SampleView that will only contain EmpID, EMPName.
We can query the view above as follows:

SELECT * FROM SampleView

It will show all the details of EmpData

Updating a View
You can update a view by using the following syntax:

SELECT column_name(s)
FROM table_name
WHERE condition

Suppose we want to add the "Item" column to the "Current Product List" view. We will update the view with the following SQL:

CREATE VIEW [Current Product List] AS
SELECT ProductID,ProductName,Item
FROM Products
WHERE Discontinued=No

Droping a View
You can delete a view by using following syntax:

DROP VIEW view_name

Corruption in Views
SQL gets corrupted due to various reasons like virus attack meta-data structure corruption, power failure, hardware problem, improper system shut down etc. You can easily restore corrupted Views from the backup, if available.

Restoration of Views
To restore your corrupt view, you will need to full backup. If you don’t have full backup you can’t restore but third party SQL database recovery software can help.

No comments:

Post a Comment