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
View < [View Name]> As
statement form table]> with where clause <optional>
Example: Creating View
create view testview as
select * from userlogininfo
OutPut: select * from testview
101 ArunKumar Vashundhra Ghaziabad firstname.lastname@example.org 22222 7607185995 Admin 1989-02-28
102 Arunsingh Vashundhra Ghaziabad email@example.com 123456 7607185995 Admin 1989-02-28
103 Varunsingh Vashundhra Ghaziabad firstname.lastname@example.org 00000 7607185995 User 1988-02-28
Example: Modify View
alter view testview
select * from
Output: select * from testview
MY SQL DATABASE
SQL SERVER DATABASE
Example: Drop View
drop view testview
Types of View:
are many types of view in SQL Server in which some important view is given as
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.
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.
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.