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

Example:

CREATE VIEW SampleView

As

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:

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

Example:

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.