What is a View in SQL Server?

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 tables. 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 tables. These tables are referred to as base or underlying tables. 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 tables. 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 BY PostedDate
GO