Creating a View
Learn how to create a SQL Server View using the CREATE VIEW statement with simple and practical examples.
Introduction
A SQL View is created using the CREATE VIEW statement.
A View contains a SELECT query that defines the data that will be displayed when the View is queried.
Once created, the View can be used like a virtual table with a normal SELECT statement.
CREATE VIEW Syntax
CREATE VIEW ViewName
AS
SELECT column1, column2, ...
FROM TableName
WHERE condition;
Explanation
- CREATE VIEW is used to create a new View.
- ViewName is the name given to the View.
- AS separates the View name from the SELECT query.
- The SELECT query defines the data returned by the View.
Example Table
Consider the following Employees table:
| EmployeeId | EmployeeName | Department | Salary |
|---|---|---|---|
| 1 | Rahul | IT | 45000 |
| 2 | Priya | HR | 40000 |
| 3 | Amit | IT | 55000 |
| 4 | Sneha | Finance | 50000 |
| 5 | Neha | IT | 60000 |
Example 1: Create a Simple View
Suppose we want a View that displays employee name and department only.
CREATE VIEW EmployeeDepartmentView
AS
SELECT
EmployeeName,
Department
FROM Employees;
Execute the View
SELECT *
FROM EmployeeDepartmentView;
Example 2: View with WHERE Clause
A View can also contain a WHERE condition. For example, we can create a View that displays only IT department employees.
CREATE VIEW ITEmployeesView
AS
SELECT
EmployeeId,
EmployeeName,
Department,
Salary
FROM Employees
WHERE Department = 'IT';
Retrieve Data
SELECT *
FROM ITEmployeesView;
Example 3: Selecting Specific Columns
A View does not need to contain every column from the original table.
CREATE VIEW EmployeeSalaryView
AS
SELECT
EmployeeName,
Salary
FROM Employees;
Now the View exposes only the employee name and salary.
SELECT *
FROM EmployeeSalaryView;
Example 4: View Using Multiple Tables
A View can also be created using a JOIN between multiple tables.
CREATE VIEW EmployeeDepartmentDetails
AS
SELECT
e.EmployeeId,
e.EmployeeName,
d.DepartmentName
FROM Employees e
INNER JOIN Departments d
ON e.DepartmentId = d.DepartmentId;
This View combines employee information with department information.
Checking the Created View
After creating a View, we can retrieve its data using a SELECT statement.
SELECT *
FROM EmployeeDepartmentView;
SQL Server executes the query defined by the View and returns the result.
Viewing Existing Views
SQL Server provides system catalog information that can be used to find Views in a database.
SELECT
name
FROM sys.views;
This query displays the names of Views available in the current database.
Important Points
- Use CREATE VIEW to create a View.
- The View definition contains a SELECT query.
- A View can select specific columns instead of all columns.
- A View can contain filtering conditions using WHERE.
- A View can be based on multiple tables using JOINs.
- After creating a View, it can be queried using SELECT.
Summary
The CREATE VIEW statement is used to create a SQL Server View. A View is based on a SELECT query and can contain selected columns, filtering conditions and data from multiple tables. Once created, it can be queried like a virtual table.