Views can be considered as virtual table. Generally speaking, a table has a set of definition, and it physically stores the data. A view also has a set of definitions, which is build on top of tables or other views, and it does not physically store the data. View is a pre-written query that is stored on the database. A view consists of a select statement, and when you run the view, you see the results of it like you would when opening a table. A view consists of rows with columns, and you can retrieve data from a view. The fields in the view’s virtual table are the fields of one or more real tables in the database. You can use views to join two tables in your database. You can also use views as a security mechanism to restrict the data available to end users. A view is created by joining one or more tables.

Syntax: Creating View

  Create View < [View Name]> As

  < [Select statement form table]> with where clause <optional>

Example: Creating View

create view testview as
select * from userlogininfo

OutPut: select * from testview

Desired Output:

101         ArunKumar         Vashundhra Ghaziabad arunbaswar@gmail.com               22222    7607185995         Admin                1989-02-28

102         Arunsingh           Vashundhra Ghaziabad arunbaswar@gmail.com               123456  7607185995         Admin                1989-02-28

103         Varunsingh         Vashundhra Ghaziabad varunbaswar@gmail.com             00000    7607185995         User                1988-02-28

Example: Modify View

alter view testview as

select * from SubCategory

Output: select * from testview

Desired Output:

ASP.NET               .NET

C#                           .NET

G5                          Carbon

MY SQL DATABASE

SQL SERVER        DATABASE

kuntal   Hello

Example: Drop View

      drop view testview

Types of View:

                There are many types of view in SQL Server in which some important view is given as follows:

1.     Standard View:

 Combining data from one or more tables through a standard view lets you satisfy most of the benefits of using views. These include focusing on specific data and simplifying data manipulation.

2.     Indexed View:

    An indexed view is a view that has been materialized. This means it has been computed and stored. You index a view by creating a unique clustered index on it. Indexed views dramatically improve the     performance of some types of queries. Indexed views work best for queries that aggregate many rows. They are not well-suited for underlying data sets that are frequently updated.

3.     Partitioned View:

    A partitioned view joins horizontally partitioned data from a set of member tables across one or more servers.

When to Use a View

You need to have a goal in mind when creating a view. There are a number of scenarios where you will want to look for a view as a solution.

·         To hide the complexity of the underlying database schema, or customize the data and schema for a set of users.

·         To control access to rows and columns of data.

·         To aggregate data for performance.