In this article, we will explore the CREATE VIEW SQL statement in SQL Server and understand how it can be used to create views. Views are virtual tables that provide a simplified and secure way to access data from underlying base tables. We will cover the syntax, examples, and use cases of the CREATE VIEW SQL statement.

Syntax

The syntax for creating a view in SQL Server is as follows:

CREATE OR ALTER VIEW schema_name.view_name WITH < view_attribute > AS select_statement [ WITH CHECK OPTION ]

The CREATE VIEW statement starts with the keyword CREATE VIEW followed by the name of the view. The view can have attributes such as ENCRYPTION, SCHEMABINDING, and VIEW_METADATA. After the attributes, the SELECT statement defines the query that will populate the view. The WITH CHECK OPTION is used to control data insertion through the view.

Example

Let’s create a sample database called SQLShackDB and create a few tables in it:

CREATE TABLE Employees (
EmployeeID INT NOT NULL,
FirstName NVARCHAR(50) NOT NULL,
MiddleName NVARCHAR(50) NULL,
LastName NVARCHAR(75) NOT NULL,
Title NVARCHAR(100) NULL,
HireDate DATETIME NOT NULL,
VacationHours SMALLINT NOT NULL,
Salary DECIMAL(19, 4) NOT NULL
);

CREATE TABLE Products (
ProductID INT NOT NULL,
Name NVARCHAR(255) NOT NULL,
Price DECIMAL(19, 4) NOT NULL
);

CREATE TABLE Sales (
SalesID UNIQUEIDENTIFIER NOT NULL,
ProductID INT NOT NULL,
EmployeeID INT NOT NULL,
Quantity SMALLINT NOT NULL,
SaleDate DATETIME NOT NULL
);

Now, let’s create a view called vEmployeesWithSales that returns a list of employees who have made a sale:

CREATE VIEW vEmployeesWithSales AS
SELECT DISTINCT Employees.*
FROM Employees
JOIN Sales ON Employees.EmployeeID = Sales.EmployeeID;

We can test the view by executing the SELECT statement:

SELECT * FROM vEmployeesWithSales;

The result will be a list of employees who have made a sale. The view can be treated like a table and can be queried just like any other table in SQL Server.

Conclusion

The CREATE VIEW SQL statement in SQL Server allows us to create virtual tables that simplify data access and provide security mechanisms. In this article, we covered the syntax and example of creating a view using the CREATE VIEW statement. In the next article, we will explore more advanced topics such as modifying views and working with indexed views.