Introduction
In this article, we are going to see how to use the CREATE VIEW SQL statement to create a view. This is the first article in a series that will be a programmatical approach of creating, altering and working with views using T-SQL. If you are a beginner and you don’t even know what a view is, don’t worry. We will kick off with a definition, and then move on to some basics like syntax, examples, use cases, etc.
A view is simply a virtual table. Think of it as just a query that is stored on SQL Server and when used by a user, it will look and act just like a table but it’s not. It is a view and does not have a definition or structure of a table. Its definition and structure is simply a query that, under the hood, can access many tables or a part of a table.
Views can be used for a few reasons. Some of the main reasons are as follows:
- To simplify database structure to the individuals using it
- As a security mechanism to DBAs for allowing users to access data without granting them permissions to directly access the underlying base tables
- To provide backward compatibility to applications that are using our database
Having said that, those reasons are a topic for designing views which we will not touch in this series. In this article, we are going to go through the CREATE VIEW SQL syntax, see what views are all about, and what we can do with them.
Syntax
|
1 2 3 4 |
CREATE OR ALTER VIEW schema_name.view_name WITH <view_attribute> AS select_statement [WITH CHECK OPTION] |
We all know how complicated syntax can get but this is not the case with views. A view can be created by saying CREATE VIEW followed by a name WITH view attributes:
- ENCRYPTION – Using this attribute prevents the view from being published as part of SQL Server replication
- SCHEMABINDING – Binds the view to the schema of the underlying table. We will use this one in another article when indexing a view
- VIEW_METADATA – Causes SQL Server to return to the DB-Library, ODBC, and OLE DB APIs the metadata information about the view
After the AS, it goes the actual SELECT statement that defines the query. This is usually the bulk of a query AKA the DML statement that is going to make the view and its results.
The WITH CHECK OPTION is very useful when inserting data through a view. When a row is modified through a view, this option gives us control over inserted data into the table that follows the WHERE clause in the view’s definition. More about this in the upcoming article.
CREATE VIEW SQL statement
Without further ado, let’s fire up SQL Server Management Studio and start working on views. Before we use the CREATE VIEW SQL statement, let’s create a new database from Object Explorer called SQLShackDB, and then create a few tables in it by running the script from below:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
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 ); GO CREATE TABLE Products (ProductID INT NOT NULL, Name NVARCHAR(255) NOT NULL, Price DECIMAL(19, 4) NOT NULL ); GO CREATE TABLE Sales (SalesID UNIQUEIDENTIFIER NOT NULL, ProductID INT NOT NULL, EmployeeID INT NOT NULL, Quantity SMALLINT NOT NULL, SaleDate DATETIME NOT NULL ); GO |
Now, that we have our sample database with tables in it, we can create a view called vEmployeesWithSales using the script from below as an example:
|
1 2 3 4 5 6 7 8 9 |
USE SQLShackDB; GO CREATE VIEW vEmployeesWithSales AS SELECT DISTINCT Employees.* FROM Employees JOIN Sales ON Employees.EmployeeID = Sales.EmployeeID; GO |
This is a simple view with a simple SELECT statement that returns a list of employees that have a sale. As a matter of fact, you can always test the query before creating the view by executing only the SELECT part of the CREATE VIEW SQL statement and it’s a good idea to see if the query will return something. Make sure that you are connected to the appropriate database first, then mark the SELECT part of the code, and hit Execute:
The query returns no result because we don’t actually have any data in our new tables, but you can see the list of columns that returned. The next thing we can do is insert some data into tables. To do this, use the following script:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
USE SQLShackDB; GO INSERT INTO Employees SELECT 1, 'Ken', NULL, 'Sánchez', 'Sales Representative', '1/1/2016', 2080, 45000; INSERT INTO Employees SELECT 2, 'Janice', NULL, 'Galvin', 'Sales Representative', '12/11/2016', 2080, 45000; INSERT INTO Products SELECT 1, 'Long-Sleeve Logo Jersey, S', 12.99; INSERT INTO Products SELECT 2, 'Long-Sleeve Logo Jersey, M', 14.99; INSERT INTO Products SELECT 3, 'Long-Sleeve Logo Jersey, L', 16.99; INSERT INTO Products SELECT 4, 'Long-Sleeve Logo Jersey, XL', 18.99; INSERT INTO Sales SELECT NEWID(), 1, 1, 4, '04/15/2016'; INSERT INTO Sales SELECT NEWID(), 2, 1, 1, '02/01/2016'; INSERT INTO Sales SELECT NEWID(), 3, 1, 2, '03/12/2016'; INSERT INTO Sales SELECT NEWID(), 2, 2, 2, '03/18/2016'; INSERT INTO Sales SELECT NEWID(), 3, 2, 1, '04/16/2016'; INSERT INTO Sales SELECT NEWID(), 4, 2, 2, '04/23/2016'; |
Just to make sure that data is inserted into our tables successfully, re-execute the SELECT part of the CREATE VIEW SQL statement and it should return the following:
Note that we are using the DISTINCT with SELECT to prevent the retrieval of duplicate records because both employees have multiple records.
Let’s get back to our view and see how it looks in our database. If we head over to Object Explorer and expand the Views folder under our demo database, we will find our view that looks exactly like a table because it has columns in it:
These are all columns that this view will return. Let’s see what happens if we treat this view as a table. Write a SELECT statement but instead of saying select everything from and then the name of a table, we will simply say from a view:
|
1 |
SELECT * FROM vEmployeesWithSales |
As can be seen from the figure above, the result is exactly the same as when querying data using actual tables.
Like any other object in SQL Server, views have properties too. In Object Explorer, right-click any view of which you want to view the properties and select Properties:
Notice that here you can see the actual options that the view was created with to understand how its data is derived from the actual tables:
- ANSI NULLs – It indicates if the object was created with the ANSI NULLs option
- Encrypted – Specifies whether the view is encrypted
- Quoted identifier – Shows if the object was created with the quoted identifier option
- Schema bound – Designates whether the view is schema-bound
- For detailed information about view’s definition and properties, see Get Information About a View
Conclusion
In this article, the goal was only to get familiar with the CREATE VIEW SQL statement syntax and creating a basic view. Moving on to a bit more complex stuff like creating a view with aggregates in it will be the focus in the next article. In other words, we are going to use the DLM language (Data Manipulation Language) and write some more advance SELECT queries.
I hope this article on CREATE VIEW SQL statement has been informative for you and I thank you for reading it. Stay tuned for the next one…
Table of contents
| CREATE VIEW SQL: Creating views in SQL Server |
| CREATE VIEW SQL: Modifying views in SQL Server |
| CREATE VIEW SQL: Inserting data through views in SQL Server |
| CREATE VIEW SQL: Working with indexed views in SQL Server |
He has written extensively on both the SQL Shack and the ApexSQL Solution Center, on topics ranging from client technologies like 4K resolution and theming, error handling to index strategies, and performance monitoring.
Bojan works at ApexSQL in Nis, Serbia as an integral part of the team focusing on designing, developing, and testing the next generation of database tools including MySQL and SQL Server, and both stand-alone tools and integrations into Visual Studio, SSMS, and VSCode.
See more about Bojan at LinkedIn
View all posts by Bojan Petrovic
- Visual Studio Code for MySQL and MariaDB development - August 13, 2020
- SQL UPDATE syntax explained - July 10, 2020
- CREATE VIEW SQL: Working with indexed views in SQL Server - March 24, 2020