Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Limitations When Working with SQL Server Views


By:   |   Last Updated: 2017-11-01   |   Comments   |   Related Tips: More > 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:

Msg 102, Level 15, State 1, Procedure ViewBookDetails, Line 2 [Batch Start Line 61]
Incorrect syntax near '@Price'.
Msg 137, Level 15, State 2, Procedure ViewBookDetails, Line 6 [Batch Start Line 61]
Must declare the scalar variable "@Price".

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:

id name price
1 Book1 200
4 Book4 230
5 Book5 150
7 Book7 200
8 Book8 190
11 Book11 200
12 Book12 120

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:

The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.

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:

id name price
12 Book12 120
5 Book5 150
8 Book8 190

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:

Views or functions are not allowed on temporary tables. Table names that begin with '#' denote temporary tables.

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.


Last Updated: 2017-11-01


next webcast button


next tip button



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

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools