Limitations of Views in SQL Server

Limitations of Views in SQL Server with Examples

In this article, I am going to discuss the Limitations of Views in SQL Server with examples. Please read our previous article before proceeding to this article where we discussed the Indexed View in SQL Server with examples. This is one of the most frequently asked interview questions in SQL Server. So at the end of this article, you will definitely understand the disadvantages of limitations of views in SQL Server.

Let us discuss the limitations of views in SQL Server:

We are going to use the following Employee table to understand the limitations of views in SQL Server.

Limitations of Views in SQL Server

Please use the below SQL Script to create and populate the Employee table with some test data.

-- Create Employee Table
CREATE TABLE Employee
(
  ID INT PRIMARY KEY,
  Name VARCHAR(50),
  Gender VARCHAR(50),
  DOB DATETIME,
  Salary DECIMAL(18,2),
  DeptID INT
)
GO

-- Populate the Employee Table with test data
INSERT INTO Employee VALUES(1, 'Pranaya', 'Male','1996-02-29 10:53:27.060', 25000, 1)
INSERT INTO Employee VALUES(2, 'Priyanka', 'Female','1995-05-25 10:53:27.060', 30000, 2)
INSERT INTO Employee VALUES(3, 'Anurag', 'Male','1995-04-19 10:53:27.060',40000, 2)
INSERT INTO Employee VALUES(4, 'Preety', 'Female','1996-03-17 10:53:27.060', 35000, 3)
INSERT INTO Employee VALUES(5, 'Sambit', 'Male','1997-01-15 10:53:27.060', 27000, 1)
INSERT INTO Employee VALUES(6, 'Hina', 'Female','1995-07-12 10:53:27.060', 33000, 2)
GO
Let’s discuss the Limitations of Views in SQL Server:

In SQL Server, it is not possible to pass parameters to a view. The Inline Table Valued Functions in SQL Server are an excellent replacement for the parameterized views.

-- Error: Cannot pass Parameters to a View
CREATE VIEW vwEmployeeDetailsByGender
@Gender varchar(20)
AS
SELECT Id, Name, Gender, DOB, Salary, DeptID
FROM  Employee
WHERE Gender = @Gender
The Table-Valued functions in SQL Server can be used as a replacement for parameterized views.
CREATE FUNCTION fnEmployeeDetailsByGender
(
  @Gender VARCHAR(20)
)
RETURNS Table
AS
RETURN  
(SELECT Id, Name, Gender, DOB, Salary, DeptID
FROM Employee 
WHERE Gender = @Gender)

-- Calling the function
SELECT * FROM dbo.fnEmployeeDetailsByGender('Male')

It will give us the below output

Limitations of Views in SQL Server

Rules and Defaults cannot be associated with the views in SQL Server. This is because the views do not store the data physically they are actually Saved SQL statements. Therefore, it makes sense that they do not have any support for Rules and Defaults.

The ORDER BY clause is invalid in views unless TOP, OFFSET or FOR XML is also specified.

CREATE VIEW vwEmployeeDetailsSortedByName
AS
SELECT Id, Name, Gender, DOB, Salary, DeptID
FROM  Employee
ORDER BY Name

If we use ORDER BY, we will get an error stating – 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.

Let’s see how to use TOP Clause to support Order By clause in SQL Server.
CREATE VIEW vwEmployeeDetailsSortedByName
AS
SELECT TOP 100 PERCENT Id, Name, Gender, DOB, Salary, DeptID
FROM  Employee
ORDER BY Name

To learn more about TOP along with PERCENT please find the below article.

https://dotnettutorials.net/lesson/top-n-clause-sql-server/

Views cannot be created based on temporary tables.

Let’ s see an example to understand this.

Create Table ##TestTempTable(Id int, Name nvarchar(20), Gender nvarchar(10))

Insert into ##TestTempTable values(101, ABC, 'Male')
Insert into ##TestTempTable values(102, PQR, 'Female')
Insert into ##TestTempTable values(103, XYZ, 'Female')

-- Error: Cannot create a view on Temp Tables
Create View vwOnTempTable
as
Select Id, Name, Gender
from ##TestTempTable
So, in short, the following are the limitations of Views in SQL Server
  1. We cannot pass parameters to SQL Server views
  2. Cannot use an Order By clause with views without specifying FOR XML, OFFSET or TOP
  3. The Views cannot be created based on Temporary Tables in SQL Server
  4. We cannot associate Rules and Defaults with SQL Server views

In the next article, I am going to discuss the DML Triggers in SQL Server with examples.

SUMMARY

In this article, I try to explain the Limitations of Views in SQL Server with examples. I hope this article will help you with your need. I would like to have your feedback. Please post your feedback, question, or comments about this article.

Leave a Reply

Your email address will not be published. Required fields are marked *