View in SQL Server

In this SQL Server tutorial, we are going to learn about a view in SQL Server. And we also cover various methods to create a SQL Server View. Here is the complete list of topics that we will discuss.

• What is a view in SQL Server
• Why do we use view in SQL Server
• How to create a view using a script in SQL Server
• What type of privileges are required to create a view in SQL Server database
• How to create and set a field in a view SQL Server
• How to create view in SQL Server
• How to create view in SQL Server with parameter
• How to create view in SQL Server management studio
• How to create updatable view in SQL Server
• Types of views in SQL Server
• How to create materialized view in SQL Server
• How to create indexed view in SQL Server
• How to create parameterized view in SQL Server

What is a view in SQL Server

In SQL Server, a VIEW is just like a virtual table that holds data from one or more than one table. A view includes a set of SQL queries for retrieving data from the database. And it does not even exist in the database physically. Moreover, a view name, like the name of a SQL table, should be unique in the database.

A query used for a view in SQL Server can be based on one or many tables. And it can also be based on a view in the existing or some other databases.

By using a view in SQL Server, we can also manage the security mechanism. Users can access data from the VIEW without requiring permission for each table or column.

Read SQL Server bulk insert from CSV file

Why do we use view in SQL Server

A view is simply a SQL statement that combines the columns of a table in SQL Server. And we generally utilize a view in SQL Server because it offers the following advantages.

• A SQL Server VIEW offers an extra layer of table security by limiting access to a species collection of rows and columns in the table.
• A view in SQL Server also helps in hiding the complexities of performing joins between multiple tables.
• Views also allow users to pull data from various tables without actually joining them.
• Views also helps to rename columns without altering the base table in SQL Server. However, the number of columns in the view and number of columns in the select statement should be equal. As a result, renaming the columns of the basic tables helps to hide their names.
• In SQL Server, views can store complex queries. Moreover, various users can create different views on the same table.

Read SQL Server function return table

What type of privileges are required to create a view in SQL Server database

In this section, we will explain what privileges and permissions do a user require to create a view in SQL Server.

First, let’s see what permissions do a user needs to create a SQL Server view.

• First, a user role must have CREATE VIEW permission in the database.
• Second, a role must have ALTER permission on the schema, where a view is created.

Now, let’s understand how we can grant these required permissions in SQL Server using the query.

USE [sqlserverguides];
CREATE ROLE [user_role1];
GRANT SELECT TO [user_role1];
GRANT CREATE VIEW TO [user_role1];
GRANT ALTER ON SCHEMA::[dbo] TO [user_role1];

• In the above query, first, we are creating a user role in the sqlserverguides database with the name user_role1.
• After this, we are using the GRANT SELECT statement to grant the select permission to the new role.
• Next, we are using the GRANT CREATE VIEW statement to grant create view permission to the new role.
• In the end, we are using the GRANT ALTER statement to grant the ALTER dbo schema permission to the new role.

Next, let’s understand some of the restrictions while using a view in SQL Server.

• We can create a SQL Server view only using the current database.
• A View in SQL Server can have a total of 1,024 columns only.

Read Arithmetic operators in SQL Server

How to create view in SQL Server

Now that we got a clear idea of what are views in SQL Server. Next, let’s understand how to create a user-defined view in SQL Server.

In SQL Server, there are 2 common ways to create any database object, and the same is the case with views. The first way is to use the GUI application SQL Server Management Studio, and the second way is to use the Transact-SQL query.

Let’s understand each way of creating a user-defined view in SQL Server using an example.

How to create view in SQL Server management studio

Here are some steps that we can follow to create a user-defined view using SQL Server Management Studio.

• After running the SQL Server Management Studio, first, connect to a Database instance.
• Next, from the Object Explorer, expand the required database where you want to create a view.
• After this, right-click the Views directory and click on “New View“. It will open a new Add Table dialog box.

• Now, from the dialog box, we can select the required element for the new view. These elements include Tables, Functions, Synonyms, and even Views.

• After selecting all the required elements, first, click on “Add” and then, click on “Close“.
• Next, from the Diagram Pane, we can tick mark the required element or column that we want in the view.
• Moreover, from the Criteria Pane, we can even select the Sort or filter options.

• In the end, use the Save option to save the newly created view with a specified name.

With this, we have successfully created a view in the sqlserverguides database

Read SQL Operand data type real is invalid for modulo operator

How to create a view using a script in SQL Server

Now, let’s understand how to create a view in SQL Server using a SQL query or script. But, for this task, first, let’s understand the syntax of CREATE VIEW statement in SQL Server

CREATE VIEW view_name AS
SELECT column_1, ..., column_n
FROM table_name
WHERE condition;

• In the syntax, we are using the CREATE VIEW statement to create a simple user-defined view in SQL Server.
• Now, after CREATE VIEW keyword, we have to specify the name of the view instead of view_name. And then, we are using the AS keyword.
• After this, we can use the SELECT statement to fetch some columns from a table. Moreover, we can also specify any sort of condition with the SELECT statement.

Now, let’s bring together all the things from the syntax and create a user-defined view in SQL Server.

USE [sqlserverguides]
GO

CREATE VIEW US_CustomerView AS
SELECT customer_name AS [Customer Name],
       city AS [Customer City],
	   country AS [Customer Country]
FROM Customers
WHERE country = 'United States';

In the above example, we have created a view in the sqlserverguides database with the name US_CustomerView. And this view will return the customer name, city, and country from the Customers table where the country is the United States.

Now, to simply get the result, we can use the SELECT statement to fetch columns from view.

USE [sqlserverguides]
GO

SELECT * FROM US_CustomerView

Here is the image showing the final result of the above query.

Read Azure SQL Database Schema

How to create and set a field in a view SQL Server

Till now, we have seen how to create a view in SQL Server using different methods. Now, let’s undergo a little bit in detail and understand how to set fields or column names while creating a view.

While creating a view in SQL Server, we generally specify multiple columns in the definition. Now, as we are using a SELECT statement, we can also use aliases to give a specific custom name to a column in the result.

Let’s understand this situation using an example in SQL Server.

CREATE OR ALTER VIEW [dbo].[CA_CustomerView] AS
SELECT customer_name AS [Customer Name],
       country AS [Customer Country],
       country_code as [Country Code]
FROM Customers
WHERE country = 'Canada';

In the above example, we have simply created a view that will return Customer records from the Customers table where the country name is Canada. Now, take a look at how we have specified the custom column names using AS keyword. These custom names will be replaced with original column names in the final result.

USE [sqlserverguides]
GO

SELECT * FROM CA_CustomerView

After executing the above query, we will get the following result.

Alternative Method to set columns names

Other than an alias, there is an alternative to set fields or column names while creating a view in SQL Server. Let’s understand the alternative method also using an example.

CREATE OR ALTER VIEW [dbo].[US_CustomerView]
( [Customer Name], [Customer City], [Customer Country] )
AS
SELECT customer_name,
       city,
       country 
FROM Customers
WHERE country = 'United States';

In the example, after specifying the name of the view, we have specified the custom column names in parentheses. And then, we are simply following the syntax and using the AS keyword and SELECT statement. And this script will also replace the custom column names with original ones. Here is the final result.

Read How to test stored procedure in SQL Server

How to create updatable view in SQL Server

Here, we will illustrate how to create an updatable view in SQL Server. Now, by an updatable view, we mean using a view to update the data of an underlying table in SQL Server.

Now, an updatable view in SQL Server is simply a user-defined view that follows the following set of conditions.

• While making any changes using UPDATE, INSERT, or DELETE statement must point to columns from only one underlying table.
• The altered columns must refer to the underlying data in the table columns directly. There is no alternative way to obtain the columns. And we should ignore using aggregate functions like MAX(), AVG(), etc. Moreover, we should also not make any computation using JOIN, UNION, etc.
• GROUP BY, HAVING, and DISTINCT clauses should no impact the selected columns for updatation.
• We should utilize the TOP option and WITH CHECK OPTION clause together anywhere in the SELECT statement.

Now that we have gone through all the required conditions for an updatable view. Let’s create and understand how to use updatable view in SQL Server.

USE [sqlserverguides]
GO

CREATE VIEW UpdatableView AS
SELECT customer_name AS [Customer Name],
       city AS [Customer City],
	   country AS [Customer Country]
FROM Customers

In the above example, we created a simple view that fetches data from the Customers table. And we will get the following data as a result.

After creating a view, let’s understand how to use this view to update table data. And in this case, we will update the data of the Customers table. Now, for this implementation, we will use the following SQL query.

USE [sqlserverguides] ;   
GO  
UPDATE dbo.UpdatableView  
SET [Customer City] = 'Houston'   
WHERE [Customer Name] = 'Irina De Haven'; 

In the query, we are updating the city name to Houston, where the customer name is Irina De Haven. And now, if we query the view and table, we can notice that the entry is modified.

Read Bulk loading data to Azure SQL

How to create view in SQL Server with parameter

In SQL Server, we cannot create a user-defined view with parameters because a view does not accept any parameter. But, there are some alternatives available in SQL Server for this task.

The first option is to use a stored procedure in SQL Server with parameters. And the second option is to use a function in SQL Server.

Types of views in SQL Server

In this section, we will learn about different types of views in SQL Serer and we will also illustrate examples related to each type.

Now, SQL Server supports 2 main categories of views, the first is system-defined views, and send is user-defined views. Further, these types are also divided into different types.

• System-defined View
  • Information Schema View
  • Catalog View
  • Dynamic Management View
• User-defined View
  • Simple View
  • Complex View

Let’s discuss each type of view in SQL Server in more detail.

Read SQL Server scheduled stored procedure

System-defined Views in SQL Server

As the name suggests, the system-defined views in SQL Server are views that already exist in a SQL Server instance. And usually, these views exist in the master database. Moreover, there are almost 230 system-defined views in SQL Server.

And all the system-defined views in SQL Server are distributed among 3 types, named information schema view, the catalog view, and the dynamic management view.

Next, let’s illustrate each category under the system-defined view in SQL Server.

Information Schema View in SQL Server

In SQL server, there are almost twenty-one different information schema views available. The main aim of these views is to show the data related to different database objects, such as tables, constraints, columns, views.

Moreover, the name of these views always consists of INFORMATION_SCHEMA as a prefix, followed by the view name.

Catalog View in SQL Server

The main aim of catalog views in SQL Server is to return information used by a Database engine. These types of views are primarily used to offer information on the metadata of a database and its objects, such as tables and views.

However, they also omit information on backup, replication, and maintenance plans.

Dynamic Management View

The Dynamic Management View in SQL Server was first introduced in 2005. These views help the administrator to obtain information about the server state in order to diagnose problems, monitor the health of the server instance, and optimize performance.

Now, these views are further available in two parts in SQL Server. Database-scoped Dynamic Management Views are available in each database, whereas Server-scoped Dynamic Management Views are only stored in the Master database.

User-defined Views in SQL Server

As the name suggests, the user-defined views in SQL Server are views that are defined or created by a user. Moreover, these views are further categorized into 2 different types. Let’s illustrate them in more detail.

Simple View in SQL Server

A simple user-defined view in SQL Server consists of only one base table. This type of view in SQL Server can also hold data from one table. Moreover, DML operations can get executed using Simple View in SQL Server.

Operations like INSERT, DELETE, and UPDATE can get directly executed on these views. But, these views can not utilize grouping functions like COUNT(), MAX(), etc.

Till now, we have illustrated many examples related to a simple user-defined view in SQL Server. Most of the examples illustrated in the previous sections are simple user-defined views.

Complex View in SQL Server

The complete user-defined views in SQL Server are views that can have multiple tables. Moreover, these views can perform grouping of data using group by clause, join conditions, and an order by clause.

However, these views cannot be employed to perform DML operations in SQL Server. Operations like INSERT, DELETE, and UPDATE cannot get directly executed on these views.

Let’s understand how to create a complex view in SQL Server using an example. And for the example demonstration, we will use the following tables.

Next, we will create a complex user-defined view that will perform an inner join on the Customers and Orders table.

USE [sqlserverguides]
GO

CREATE VIEW CustomerOrderView 
AS  
SELECT Customers.id, Customers.customer_name, Customers.country 
FROM Customers 
INNER JOIN Orders
ON Customers.id=Orders.customer_id

In the above example, we have created a complex view with the name “CustomerOrderView“. In this view, we are using the inner join between the Customers and Orders table. Therefore, when we query this function, we will get the following result.

Read SQL Server stored procedure case statement

How to create materialized view in SQL Server

Views in SQLServer are virtual tables made up of the results of a SQL query. It also enables to hide the complexity of SQL queries, resulting in a level of abstraction. Moreover, a view also offers various benefits like joining multiple tables, abstracting table data, etc.

But, simple user-defined views are not effective in every situation. For example, if a view aggregates millions of rows, the query performance of a view will degrade. Thus, the materialized views come handly in terms of performance.

Materialized views in SQL Server are views that store query’s result set as a permanent data object, such as a table. Moreover, these views get frequently updated from the underlying base tables whenever any modifications occur. And these are useful in creating complex SQL queries for business or application logic.

Now that we got a clear idea about materialized views in SQL Server, it’s time to understand how to create them.

SQL Server sets it apart in the way it implements materialized views. SQL Server utilizes the concept of indexed views to create materialized views. So, to create a materialized view in SQL Server, we must create an indexed view.

Read Azure SQL database configure firewall

How to create indexed view in SQL Server

An indexed view in SQL Server is a materialized view where the view definition has been computed, and the resulting data is stored similar to a table.

Indexed views help to enhance the performance of some queries significantly. Moreover, for queries that aggregate a large number of rows, indexed views are the best option.

Now, let’s understand how to create an indexed view in SQL Server. And to do so, we have to follow the following steps in SQL Server.

• To begin, create a view with the WITH SCHEMABINDING option, which links the view to the underlying tables’ schema.
• Then, on the view, construct a unique clustered index.

Now, use these both steps and understand the syntax to create an indexed view in SQL Server

/* Creating a view in SQL Server */
CREATE VIEW view_name 
WITH SCHEMABINDING AS    
  select_statement
  
/* Creating an index on view */
CREATE UNIQUE CLUSTERED INDEX index_name ON view_name(id)

Now, let’s use this syntax to create an indexed view in SQL Server. And for the example demonstration, consider the following tables and their relations.

First, for the example demonstration, we will create a view using the WITH SCHEMABINDING option. Here is the query for this task.

USE [sqlserverguides]
GO

CREATE VIEW student_master
WITH SCHEMABINDING
AS 
SELECT Students.student_id,Students.first_name, Marks.marks, 
       Marks.status, Details.City, Details.email
FROM dbo.Students INNER JOIN dbo.Marks ON Students.student_id = Marks.student_id 
                  INNER JOIN dbo.Details ON Details.college_id = Marks.college_id;

In the above example, we are implementing the INNER JOIN between 3 tables. And we are to fetch columns from these different tables.

After creating a view in SQL Server, we also have to create a unique clustered index on this view. And to execute this task, we will use the following query.

USE [sqlserverguides]
GO

CREATE UNIQUE CLUSTERED INDEX 
    ucidx_student_id 
ON dbo.student_master(student_id);

By executing the above query, we are creating a unique clustered index on the student_master view. And for the unique column, we are using student_id. And now, this view is materialized in SQL Server.

In the end, let’s query this indexed view and also review the query I/O cost statistics.

Read Rename stored procedure in SQL Server

How to create parameterized view in SQL Server

The term “parameterized view” refers to the ability to pass a value to the view in order to obtain some data. Now, this task is feasible in many systems like FoxPro, MS Access, where values can be supplied later by prompting the user or programmatically. But this task is not supported in SQL Server.

In SQL Server, views are just the result of predefined queries. Moreover, we can also use views to perform INSERT, DELETE and even UPDATE operations. But, views do not accept any parameters, and they are not applicable for such implementations.

Still, there are many alternatives available in SQL Server. For example, table-valued functions and stored procedures.

Related sql server tutorials:

• Azure SQL database column encryption
• How to delete a view in SQL Server
• Could not find stored procedure in SQL Server
• Create stored procedures in Azure SQL database

So, in this tutorial, we have learned about a view in SQL Server. And we have also covered various methods to create a SQL Server View. Here is the complete list of topics that we have discussed.

• What is a view in SQL Server
• Why do we use view in SQL Server
• How to create a view using a script in SQL Server
• What type of privileges are required to create a view in SQL Server database
• How to create and set a field in a view SQL Server
• How to create view in SQL Server
• How to create view in SQL Server with parameter
• How to create view in SQL Server management studio
• How to create updatable view in SQL Server
• Types of views in SQL Server
• How to create materialized view in SQL Server
• How to create indexed view in SQL Server
• How to create parameterized view in SQL Server