Views in SQL Server

A view is a virtual table. It is used to summarize data from various tables that helps to generate reports. A view always shows updated data.

1. CREATE VIEW

  • SQL VIEW is a virtual table and it contains rows and columns just like real table.
  • The fields of views are selected from one or more real tables in the database.
  • The views are used to filter the data in the table and to hide some columns data from a database table.

Syntax

CREATE VIEW view_name AS
SELECT column_name
FROM table name
WHERE [condition]


Example: Query using CREATE VIEW Statement

Consider the following table titled  'Students'.

Stud_IDStud_NameCityCountry
1MarkLondonEngland
2AlexParisFrance
3BobSydneyAustralia
4JayaDelhiIndia
5SurendraBarodaIndia

Write a query to create a view of columns such as Stud_ID, Stud_Name, City from the above table 'Students'.

CREATE VIEW Stud AS
SELECT Stud_ID, Stud_Name
FROM Students
WHERE Country='India'


Result is shown in the following table:

Stud_IDStud_Name
4Jaya
5Surendra

Note: SELECT * FROM students; statement is used to see the created view.

2. Update VIEW

User can modify the view in SQL server without dropping it by using the ALTER VIEW statement.

Syntax

ALTER VIEW [Schema_name] AS
SELECT expression  
FROM table_name
Where [condition]


Example: Update view using ALTER VIEW statement.

Consider the following table titled  'Students'.

Stud_IDStud_NameCityCountry
1MarkLondonEngland
2AlexParisFrance
3BobSydneyAustralia
4JayaDelhiIndia
5SurendraBarodaIndia

To update view titled 'Stud', the query should be written as:

ALTER VIEW Stud AS
SELECT Stud_ID, Stud_Name
FROM Students
WHERE Country = 'Australia'


Result is shown in the following table.

Stud_IDStud_Name
3Bob

3. SQL Server DROP VIEW

The SQL Server DROP VIEW Statement is used to delete or remove the view.

Syntax:
DROP VIEW View_name;

Example: Query using DROP VIEW Statement.

If user needs to delete view titled as 'Students'. The query should be written as:

DROP VIEW Students
▲