Rajendra Gupta

SQL View – A complete introduction and walk-through

July 1, 2019 by

In relational databases, data is structured using various database objects like tables, stored procedure, views, clusters etc. This article aims to walk you through ‘SQL VIEW’ – one of the widely-used database objects in SQL Server.

It is a good practice to organize tables in a database to reduce redundancy and dependency in SQL database. Normalization is a database process for organizing the data in the database by splitting large tables into smaller tables. These multiple tables are linked using the relationships. Developers write queries to retrieve data from multiple tables and columns. In the query, we might use multiple joins and queries could become complicated and overwhelming to understand. Users should also require permissions on individual objects to fetch the data.

Let’s go ahead and see how SQL VIEW help to resolve these issues in SQL Server.

Introduction

A VIEW in SQL Server is like a virtual table that contains data from one or multiple tables. It does not hold any data and does not exist physically in the database. Similar to a SQL table, the view name should be unique in a database. It contains a set of predefined SQL queries to fetch data from the database. It can contain database tables from single or multiple databases as well.

In the following image, you can see the VIEW contains a query to join three relational tables and fetch the data in a virtual table.

SQL View image

A VIEW does not require any storage in a database because it does not exist physically. In a VIEW, we can also control user security for accessing the data from the database tables. We can allow users to get the data from the VIEW, and the user does not require permission for each table or column to fetch data.

Let’s explore user-defined VIEW in SQL Server.

Note: In this article, I am going to use sample database AdventureWorks for all examples.

Create a SQL VIEW

The syntax to create a VIEW is as follows:

Example 1: SQL VIEW to fetch all records of a table

It is the simplest form of a VIEW. Usually, we do not use a VIEW in SQL Server to fetch all records from a single table.

Once a VIEW is created, you can access it like a SQL table.

SQL View to fetch all records of a table

Example 2: SQL VIEW to fetch a few columns of a table

We might not be interested in all columns of a table. We can specify required column names in the select statement to fetch those fields only from the table.

Example 3: SQL VIEW to fetch a few columns of a table and filter results using WHERE clause

We can filter the results using a Where clause condition in a Select statement. Suppose we want to get EmployeeRecords with Martial status ‘M’.

Example 4: SQL VIEW to fetch records from multiple tables

We can use VIEW to have a select statement with Join condition between multiple tables. It is one of the frequent uses of a VIEW in SQL Server.

In the following query, we use INNER JOIN and LEFT OUTER JOIN between multiple tables to fetch a few columns as per our requirement.

Suppose you need to execute this query very frequently. Using a VIEW, we can simply get the data with a single line of code.

View in SQL Server to fetch records from multiple tables

Example 5: SQL VIEW to fetch specific column

In the previous example, we created a VIEW with multiple tables and a few column from those tables. Once we have a view, it is not required to fetch all columns from the view. We can select few columns as well from a VIEW in SQL Server similar to a relational table.

In the following query, we want to get only two columns name and contract type from the view.

Example 6: Use Sp_helptext to retrieve VIEW definition

We can use sp_helptext system stored procedure to get VIEW definition. It returns the complete definition of a SQL VIEW.

For example, let’s check the view definition for EmployeeRecords VIEW.

Use Sp_helptext to retrieve view definition

We can use SSMS as well to generate the script for a VIEW. Expand database -> Views -> Right click and go to Script view as -> Create To -> New Query Editor Window.

SSMS to generate the script for a view

Example 7: sp_refreshview to update the Metadata of a SQL VIEW

Suppose we have a VIEW on a table that specifies select * statement to get all columns of that table.

Once we call the VIEW DemoView, it gives the following output.

sp_refreshview to update the Metadata of a SQL View

Let’s add a new column in the table using the Alter table statement.

Rerun the select statement to get records from VIEW. It should display the new column as well in the output. We still get the same output, and it does not contain the newly added column.

sp_refreshview to update the Metadata of a View in SQL Server.

By Default, SQL Server does not modify the schema and metadata for the VIEW. We can use the system stored procedure sp_refreshview to refresh the metadata of any view.

Rerun the select statement to get records from VIEW. We can see the City column in the output.

Refresh the meta data

Example 8: Schema Binding a SQL VIEW

In the previous example, we modify the SQL table to add a new column. Suppose in the production instance, and you have a view in the application. You are not aware of the changes in the table design for the new column. We do not want any changes to be made in the tables being used in the VIEW. We can use SCHEMABINDING option to lock all tables used in the VIEW and deny any alter table statement against those tables.

Let’s execute the following query with an option SCHEMABINDING.

It gives an error message.

Msg 1054, Level 15, State 6, Procedure DemoView, Line 4 [Batch Start Line 2]
Syntax ‘*’ is not allowed in schema-bound objects.

We cannot call all columns (Select *) in a VIEW with SCHEMABINDING option. Let’s specify the columns in the following query and execute it again.

We again get the following error message.

Msg 4512, Level 16, State 3, Procedure DemoView, Line 5 [Batch Start Line 1]
Cannot schema bind VIEW ‘DemoView’ because of the name ‘AdventureWorks2017.dbo.MyTable’ is invalid for schema binding.
Names must be in a two-part format, and an object cannot reference itself.

In my query, I used a three-part object name in the format [DBName.Schema.Object]. We cannot use this format with SCHEMABINDING option in a VIEW. We can use the two-part name as per the following query.

Once you have created a VIEW with SCHEMABINDING option, try to add a modify a column data type using Alter table command.

Schema Binding a View in SQL Server.

We need to drop the VIEW definition itself along with other dependencies on that table before making a change to the existing table schema.

Example 8: SQL VIEW ENCRYPTION

We can encrypt the VIEW using the WITH ENCRYPTION clause. Previously, we checked users can see the view definition using the sp_helptext command. If we do not want users to view the definition, we can encrypt it.

Now if you run the sp_helptext command to view the definition, you get the following error message.

The text for the object ‘DemoView’ is encrypted.

Example 9: SQL VIEW for DML (Update, Delete and Insert) queries

We can use SQL VIEW to insert, update and delete data in a single SQL table. We need to note the following things regarding this.

  1. We can use DML operation on a single table only
  2. VIEW should not contain Group By, Having, Distinct clauses
  3. We cannot use a subquery in a VIEW in SQL Server
  4. We cannot use Set operators in a SQL VIEW

Use the following queries to perform DML operation using VIEW in SQL Server.

  • Insert DML
  • Delete DML
  • Update DML

Example 10: SQL VIEW and Check Option

We can use WITH CHECK option to check the conditions in VIEW are inline with the DML statements.

  • It prevents to insert rows in the table where the condition in the Where clause is not satisfied
  • If the condition does not satisfy, we get an error message in the insert or update statement

In the following query, we use the CHECK option, and we want only values starting with letter F in the [Codeone] column.

If we try to insert a value that does not match the condition, we get the following error message.

VIEW and Check Option

Example 11: Drop SQL VIEW

We can drop a VIEW using the DROP VIEW statement. In the following query, we want to drop the VIEW demoview in SQL Server.

Example 12: Alter a SQL VIEW

We can change the SQL statement in a VIEW using the following alter VIEW command. Suppose we want to change the condition in the where clause of a VIEW. Execute the following query.

Starting from SQL Server 2016 SP1, we can use the CREATE or ALTER statement to create a SQL VIEW or modify it if already exists. Prior to SQL Server 2016 SP1, we cannot use both CREATE or Alter together.

Conclusion

In this article, we explored SQL View with various examples. You should be familiar with the view in SQL Server as a developer or DBA as well. Further, you can learn more on how to create view in SQL Server and SQL Server indexed view. If you have any comments or questions, feel free to leave them in the comments below.

Rajendra Gupta

Rajendra Gupta

Rajendra has 8+ years of experience in database administration having a passion for database performance optimization, monitoring, and high availability and disaster recovery technologies, learning new things, new features.

While working as a Senior consultant DBA for big customers and having certified with MCSA SQL 2012, he likes to share knowledge on various blogs.
He can be reached at rajendra.gupta16@gmail.com

View all posts by Rajendra Gupta
Rajendra Gupta
348 Views