How to call a view in SQL Server

In this SQL Server tutorial, we will learn how to call a view in SQL Server using some examples. Additionally, we will also cover the following set of topics.

• How to call a view in SQL Server
• How to execute a view in SQL Server Management Studio
• How to call a function in view SQL Server
• How to call a stored procedure from a view in SQL Server
• How do I run a view in SQL Server Management Studio
• How do you run a view in SQL Server
• How to make a view run faster in SQL Server

How to call a view in SQL Server

A view in SQL Server is simply a SQL statement stored in the database with a name. It is a specified SQL query made up of columns from a table.

A view in SQL Server can be built from a single table or multiple tables, depending on the requirements. Moreover, it can hold all of a table’s rows or only a subset of them.

However, before we learn how to call a view in SQL Server, we need to understand how to create a view. And for this task, we have to use the CREATE VIEW statement. For more information, refer to “View in SQL Server“.

Now, to call or evoke a view in SQL Server, we can use the SELECT statement just like we use it with a table. Moreover, we can also use the SELECT statement to fetch specific data from a view.

Example

Now, let’s understand how to call a view in SQL Server using an example. And for this, first, we will create a simple view using the following query.

USE [sqlserverguides]
GO

CREATE VIEW [dbo].[USA_CustomerView]
AS
SELECT id, customer_name, city, country
FROM Customers
WHERE country = 'United States'
GO

In the above query, we have created a simple view that fetches records from the Customers table where the country is the United States.

Next, let’s see how to call this view in SQL Server to get the result. And for this task, we will use the SELECT statement. Now, with the SELECT statement, we can either select all the columns or some selected columns from the view.

USE [sqlserverguides]
GO

SELECT * FROM [dbo].[USA_CustomerView]
 

USE [sqlserverguides]
GO

SELECT id, customer_name, city FROM [dbo].[USA_CustomerView]

Read: How to delete a view in SQL Server

How to execute a view in SQL Server Management Studio

Till now, we have seen how to call a view in SQL Server using a SQL query. Now, we will learn how to execute a view in SQL Server Management Studio. And the steps for this implementation are as follows.

• First, run SQL Server Management Studio and connect to the required database instance.
• Next, from the Object Explorer, first, expand the required Databases directory.
• Then, expad the Views directory under the database. And all the views in that database will be listed under the Views directory.
• In the end, right-click the required view in SQL Server and click on the “SELECT TOP 1000 Rows“.

• This will automatically generate and run a SELECT statement on view fetching top 1000 rows.

Read: How to see view definition in SQL Server

How to call a function in view SQL Server

In this section, we will learn how to use and call a function within a SQL Server View.

So, as discussed earlier, a view in SQL Server is simply a SQL statement stored in the database with a name. Now, this SQL statement can be utilized to query a function within a view.

Now, for better understanding, let’s understand the concept using an example in SQL Server. And for this task, first, we will create a function using the following query.

USE [sqlserverguides]
GO

CREATE FUNCTION [dbo].[GetCustByCountry](@country VARCHAR(50))  
RETURNS TABLE  
AS  
RETURN  
    SELECT * FROM Customers WHERE country=@country 
GO

In the above query, we are creating an inline table-valued function with the name “GetCustByCountry“. Now, this function takes a country name as a parameter and returns the records of that specified country from the Customers table.

Once the function is created, we will create a view and within that view, we will use the function. Let’s take a look at the SQL code for this task.

USE [sqlserverguides]
GO

CREATE VIEW [dbo].[GetUSCustomersView]
AS
SELECT * FROM GetCustByCountry('United States')

GO

In the above query, we are creating a view and within the view, we are using the SELECT statement to query a function. Moreover, we have also specified the parameter as the United States for the function.

So, in the end, when we query the view, we will get the records from the United States as a result.

Read: Create a table from view in SQL Server

How to call a stored procedure from a view in SQL Server

In this section, we will learn how to use and call a stored procedure within a SQL Server View. But, using a stored procedure in a view is a little different than using a function in view.

Let’s understand the whole implementation of using a stored procedure using an example. And for the example implementation, we will use the following steps.

• First, we will create a stored procedure in SQL Server Database.
• Second, enable the Data Access on the SQL Server instance.
• Third, create a view that uses the stored procedure.
• In the end, we will call the view to get the final result.

#1: Creating a stored procedure in SQL Server

Now, to create a stored procedure in SQL Server, we will use the following SQL code.

USE [sqlserverguides]
GO

CREATE PROC [dbo].[GetCustomersByCountry]
AS
BEGIN
  SELECT id, customer_name, city, country 
  FROM dbo.Customers
  WHERE country = 'United States'
END
GO

In the above query, we are creating a user-defined stored procedure function with the name “GetCustomersByCountry“. Now, this procedure returns the records from the Customers table where the country name is in the United States.

#2: Enable DATA ACCESS in SQL Server

The next step in this example implementation is to enable the DATA ACCESS for your SQL Server instance. And for this, we will use the following query.

EXEC sp_serveroption
  @server = 'Your_Server_Name', 
  @optname = 'DATA ACCESS',
  @optvalue = 'TRUE';

Once we execute the above query, we can run the following query to confirm whether the DATA ACCESS is enabled or not.

SELECT name AS [Server Name],
  is_data_access_enabled 
FROM sys.servers;

And this query will return a result similar to the out given below.

#3: Creating a view in SQL Server

Now, it’s time to create a view in SQL Server that calls a stored procedure in its body. And for this task, we will use the OPENQUERY function in SQL Server.

USE [sqlserverguides]
GO

CREATE VIEW [dbo].[spCustomerView]
AS
  SELECT *
  FROM OPENQUERY([DESKTOP-XXXXXXX], 'EXEC sqlserverguides.dbo.GetCustomersByCountry')

GO

In this query, we are creating a simple user-defined view using the CREATE VIEW statement. Now, in the body, we are using the SELECT statement with the OPENQUERY function to execute the stored procedure.

#4: Calling the view in SQL Server

So, once the view is created, we can easily call it using a SELECT statement. Here is the query to evoke the created view in SQL Server.

USE [sqlserverguides]
GO

SELECT * FROM [dbo].[spCustomerView]
GO

In the end, we will get the required result set from the Customers table.

Read: How to use union in view SQL Server

How to make a view run faster in SQL Server

Views in SQL Server are useful in a variety of ways, including encapsulating complex queries and allowing us to simplify code. Moreover, views make it easier to create queries, but they don’t increase query performance.

However, to make a view run faster, we can create an indexed view in SQL Server. Now, an indexed view in SQL Server is created by adding a unique, clustered index to a view.

Now, we already have a detailed article on what is indexed view and how to create them in SQL Server. For more information, refer to the following page- Indexed views in SQL Server.

You may also like to read the following SQL Server tutorials.

• SQL Server logical operators
• SQL Server view order by
• SQL Server function return table
• Pass parameters to view in SQL Server
• Comparison Operators in SQL Server
• How to get trigger definition in SQL Server
• How to insert a row in a view in SQL Server
• View line numbers in SQL Server Management Studio

So, in this tutorial, we have learned how to call a view in SQL Server using some examples. Additionally, we have also covered the following set of topics.

• How to call a view in SQL Server
• How to execute a view in SQL Server Management Studio
• How to call a function in view SQL Server
• How to call a stored procedure from a view in SQL Server
• How do I run a view in SQL Server Management Studio
• How do you run a view in SQL Server
• How to make a view run faster in SQL Server

Bijay

I am Bijay having more than 15 years of experience in the Software Industry. During this time, I have worked on MariaDB and used it in a lot of projects. Most of our readers are from the United States, Canada, United Kingdom, Australia, New Zealand, etc.

Want to learn MariaDB? Check out all the articles and tutorials that I wrote on MariaDB. Also, I am a Microsoft MVP.