Which view is used to Create a query?

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…

Which view methods are used to create query?

Which type is used to create a query?

Which button is used to create a query?

Can you Create a query in Design view?