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
101 ArunKumar Vashundhra Ghaziabad email@example.com 22222 7607185995 Admin 1989-02-28
102 Arunsingh Vashundhra Ghaziabad firstname.lastname@example.org 123456 7607185995 Admin 1989-02-28
103 Varunsingh Vashundhra Ghaziabad email@example.com 00000 7607185995 User 1988-02-28
Example: Modify View
alter view testview as
select * from SubCategory
Output: select * from testview
MY SQL DATABASE
SQL SERVER DATABASE
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.