What is View ?
In this article I am going to describe what is view and how to create
views in SQL Server 2005 database. A view is a virtual table that
consists of columns from one or more table. Though it is similar to a
table, it is stored in the database. It is a query stored as an object.
Hence, a view is an object that derives its data from one or more table.
These table are referred to as base or underlying table. Once you have
defined a view, you can reference it like any other table in a database.
A view serves as a security mechanism. This ensures that users are able to retrieve and modify only the data seen by them. Users cannot see or access the remaining data in the underlying tabl. A view also serves as a mechanism to simplify query execution. Complex queries can be stored in the form as a view, and data from the view can be extracted using simple queries.
A view serves as a security mechanism. This ensures that users are able to retrieve and modify only the data seen by them. Users cannot see or access the remaining data in the underlying tabl. A view also serves as a mechanism to simplify query execution. Complex queries can be stored in the form as a view, and data from the view can be extracted using simple queries.
- A view consists of a SELECT statement that stored with a database. Because views are stored as part of the database, they can be managed independently of the applications that use them.
- A view behaves like a virtual table. Since you can code a view name anywhere you can code a table name. a view is sometimes called a viewed table.
- Views can be used to restrict the data that a user is allowed to access or to present data in a form that is easy for the user to understand. In some database users may be allowed to access data only through views.
In this example I am using EmployeeData database which has these fields.
Create View :
USE [EmployeeData]
GO
/****** Object: View [dbo].[ProductionData] Script Date: 12/10/2008 23:27:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[EmpDataView] AS
SELECT EmpId,
EmpFName,
EmpLName,
EmpCity,
EmpState,
EmpCountry,
PostedDate,
EmpDescription
FROM Emp
You can execute your view like this, in this example EmpData is my View name.
Use Emp
GO
SELECT * FROM EmpDataView WHERE EmpState = 'PA' ORDER BYPostedDate
GO
Create View :
USE [EmployeeData]
GO
/****** Object: View [dbo].[ProductionData] Script Date: 12/10/2008 23:27:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[EmpDataView] AS
SELECT EmpId,
EmpFName,
EmpLName,
EmpCity,
EmpState,
EmpCountry,
PostedDate,
EmpDescription
FROM Emp
You can execute your view like this, in this example EmpData is my View name.
Use Emp
GO
SELECT * FROM EmpDataView WHERE EmpState = 'PA' ORDER BYPostedDate
GO
