Bojan Petrovic
CREATE VIEW SQL script for creating a simple view

CREATE VIEW SQL: Creating views in SQL Server

January 23, 2020 by

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

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:

T-SQL script for creating three sample tables in SQLShack database

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:

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:

CREATE VIEW SQL script for creating a simple view

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:

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:

The results returned by SELECT part of the CREATE VIEW SQL script

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:

The view in Object Explorer as a result of a successfully executed CREATE VIEW SQL statement

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:

Results of a SELECT query using the view as the source

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:

View properties window of a view in SQL Server Management Studio

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

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

Bojan Petrovic
SQL commands, SQL Server Management Studio (SSMS), T-SQL, Views

About Bojan Petrovic

Experienced QA Engineer with a demonstrated history of working in the computer software industry. Skilled in network technologies, technical support, Windows SQL Server, etc. Strong information technology professional with an AP graduate in IT Technology focused on Networks and electronic technology from the Copenhagen School of Design and Technology.

1,545 Views