Concept of View in SQL Server


Introduction

A view is defined by a query and only shows the data which is defined at the time of creation of the view. If you hide particular columns then use the view. A view hides the complexity of the underlying database schema, or customizes the data and schema for a set of users.

In this article we are showing views in SQL Server 2008. A view is virtual, the data from a view is not stored physically. It is a set of queries that, when applied to one or more tables, is stored in the database as an object. A view encapsulates the name of the table. A virtual table contains column and data from multiple tables. Views are used for security purpose in the database because it hides the name of the table and logic and provides the virtual table. A view is used to store the SELECT statement only. In the view we can modify the data and table. A view provides a convenient way to retrieve and manipulate data. A view contains rows and columns, the same as a real table. The fields in a view are fields from one or more real tables in the database. A view shows up-to-date data.

Use of a View

Views are used for security purposes because they provide encapsulation of the name of the table. Data is in the virtual table, not stored permanently. Views display only selected data.

Syntax of a View

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

Different Types of View

These are two types of views.

  • Simple View
  • Complex View   

Simple View

A Simple View is a user defined view. A Simple View takes the data from a single table and has no function. The user defined view is created by the user as needed. We are showing a Simple View using a student table.

Example

In this we have a student table and data.

stud table.gif

SQL Command use to create a view (virtual table).

create view stud

as  select studname,studid,studaddress

from student where depno='d001'

SQL Command to show a view (virtual table).

create view.gif

Complex View

A Complex View is created for multiple tables and contains functions and group data.

SQL Dropping a View

You can drop the view with the help of a Drop command.

drop view asd


drop.gif

Distinct command in a View

create view [asd] as

select distinct depno

from [student]

group by depno


group.gif

Information View

A Information View is the most important of grouped views. These are used for displaying the most physical information of a database, such as table and columns. The naming convention of this type of views is INFORMATION_SCHEMA.[View Name].


information.gif

Resources

Up Next
    Ebook Download
    View all
    Learn
    View all