Limitations When Working with SQL Server Views

By: Ben Richardson   |   Updated: 2017-11-01   |   Comments   |   Related: > Views

Problem

Views in SQL Server are nothing more than saved SQL queries. Views provide an abstraction layer to underlying data, simplifying data access. However there are certain limitations for SQL Server views. These limitations include:

• You cannot pass parameters to SQL Server views
• Cannot use an Order By clause with views without specifying FOR XML or TOP
• Views cannot be created on Temporary Tables
• You cannot associate rules and defaults with views

In order to better understand these items, a detailed explanation of these limitations will be outlined in this tip with the help of working examples.

Solution

The solution to this problem is to understand these limitations and use alternate approaches if they exist.

Create a Sample SQL Server Database

First we need to create a sample library database and add tables to it. Take a look at the following script:

CREATE Database Library;

USE Library;
CREATE TABLE Authors
(
   auth_id INT PRIMARY KEY,
   auth_name VARCHAR(50) NOT NULL,
   auth_nationality VARCHAR (50)
)

CREATE TABLE Books
(
   id INT PRIMARY KEY,
   name VARCHAR(50) NOT NULL,
   price INT,
   author_id INT
)   

The above script creates a Library database with two tables Authors and Books. The tables are related to each other by a one to many relationship where one author can have many books. Let’s insert some sample records in both the tables:

INSERT INTO Authors
VALUES (1, 'Jane', 'USA'),
(2, 'Helene', 'UK'),
(3, 'Elice', 'INDIA'),
(4, 'Jody', 'CANADA'),
(5, 'Ben', 'UK'),
(6, 'Scot', 'CHINA'),
(7, 'Josh', 'FRANCE')

INSERT INTO Books
VALUES (1,'Book1', 200, 2),
(2,'Book2', 350, 3),
(3,'Book3', 400, 5),
(4,'Book4', 230, 1),
(5,'Book5', 150, 4),
(6,'Book6', 450, 7),
(7,'Book7', 200, 6),
(8,'Book8', 190, 2),
(9,'Book9', 300, 4),
(10,'Book10', 350, 1),
(11,'Book11', 200, 6),
(12,'Book12', 120, 5)   

Limitation 1: Cannot Pass Parameters to SQL Server Views

As discussed in the problem statement, you cannot pass parameters to views in SQL server. Let’s try to create a parameterized view. Try to execute the following query:

CREATE VIEW ViewBookDetails @Price INT 
AS
SELECT id, name, price
FROM Books
WHERE price < @Price   

Here, we are passing @Price parameter to ViewBookDetails View which retrieves the id, name and price of those records where price is less than the price passed as a parameter. The above statement cannot be executed and returns following error:

Alternative Approaches

The alternative approach is to create views without parameters and then use the WHERE clause for filtering the data. Let’s create a simple view and then filter the data using the WHERE clause in the SELECT queries.

CREATE VIEW ViewBookDetails
AS
SELECT id, name, price
FROM Books   

The above query creates ViewBookDetails view. Let’s retrieve those records from the ViewBookDetails, where price is less than 300.

SELECT * 
FROM ViewBookDetails
WHERE price < 300   

The above query returns the following results:

Another solution is to use an Inline Table Valued Function instead of a table view. Take a look at the following example.

CREATE FUNCTION fnBookDetails (@Price INT)
RETURNS TABLE
AS
RETURN
(SELECT id, name, price
FROM Books WHERE price < @Price)   

Now you can pass the value for price in the SELECT statement.

SELECT * FROM fnBookDetails(300)   

Limitation 2: Cannot Use ORDER BY Clause with Views

You cannot use an ORDER BY clause in a View unless you use the TOP or FOR XML clause as well. Let’s try to modify the ViewBookDetails view by sorting the data in ascending order of the price.

ALTER VIEW ViewBookDetails
AS
SELECT id, name, price
FROM Books
ORDER BY price   

If you try to execute the above query, you will see following error:

Now let’s use TOP clause with ORDER BY Statement and see the results.

ALTER VIEW ViewBookDetails
AS
SELECT TOP 3
id, name, price
FROM Books
ORDER BY price   

Now if you select records from ViewBookDetails view, the top three records ordered by price will be retrieved as shown below:

Limitation 3: Cannot Create Views on Temporary Tables

You cannot create views on temporary tables. Let’s see this limitation in action. Create a temporary table named ##PatientTable and insert some sample records in it. Execute the following T-SQL code:

CREATE TABLE ##PatientTable
(
   id int,
   name VARCHAR (20),
   blood_group VARCHAR (20)
)

INSERT INTO ##PatientTable
VALUES (1, 'Mark', 'O+'),
(2, 'Fred', 'A-'),
(3, 'Joe', 'AB+'),
(4, 'Elice', 'B+'),
(5, 'Marry', 'O-')   

Now let’s try to create a view named ViewPatientTable on this temporary table. Execute the following script:

CREATE VIEW ViewPatientTable
AS
SELECT id, name, blood_group
FROM ##PatientTable   

You will receive following error message:

Unfortunately, there is no solution to this problem. You have to use permanent tables if you want to implement views.

Limitation 4:  Views cannot have RULES and DEFAULTS

Rules and Defaults cannot be associated with views. This is because views do not store actual data, they are merely SQL statements. Therefore, it makes sense that they do not have any RULES and DEFAULTS.

Next Steps

• This tip briefly explained different limitations of SQL Server views. The tip demonstrated these limitations with the help of working examples. From now on you should keep these limitations in mind whenever you use Views in SQL server.
• Review Temporary Tables and Table Variables to understand temporary tables.
• Review Table Valued Functions to understand table valued functions.
• Review Function Types to understand different types of functions in SQL Server.

About the author

Ben Richardson is the owner of Acuity Training, a UK based IT training business offering SQL training up to advanced administration courses.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips