By: Ben Richardson | Last Updated: 2017-11-01 | Comments | Views
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.
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:
Incorrect syntax near '@Price'.
Msg 137, Level 15, State 2, Procedure ViewBookDetails, Line 6 [Batch Start Line 61]
Must declare the scalar variable "@Price".
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.
- 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
About the author
View all my tips