Limitations of Views in SQL Server: What You Need to Know

• You cannot pass parameters to a view. Table Valued functions are an excellent replacement for parameterized views.
• Rules and Defaults cannot be associated with views.
• The ORDER BY clause is invalid in views unless TOP or FOR XML is also specified
• Views cannot be based on temporary tables.

SQL Script to create tblEmployee table:

Drop table tblEmployee
CREATE TABLE tblEmployee
(
  Id int Primary Key,
  Name nvarchar(30),
  Salary int,
  Gender nvarchar(10),
  DepartmentId int
)

Insert data into tblEmployee table

Insert into tblEmployee values (1,'Rambo', 5000, 'Male', 3)
Insert into tblEmployee values (2,'Azam', 3400, 'Male', 2)
Insert into tblEmployee values (3,'Zoe', 6000, 'Female', 1)
Insert into tblEmployee values (4,'Inza', 4800, 'Male', 4)
Insert into tblEmployee values (5,'Sofia', 3200, 'Female', 1)
Insert into tblEmployee values (6,'Samser', 4800, 'Male', 3)

1. You cannot pass parameters to a view. Table Valued functions are an excellent replacement for parameterized views.

Code:

-- Error : Cannot pass Parameters to Views
Create View vWEmployeeDetails
@Gender nvarchar(20)
as
Select Id, Name, Gender, DepartmentId
from  tblEmployee
where Gender = @Gender

Table Valued functions can be used as a replacement for parameterized views.

Create function fnEmployeeDetails(@Gender nvarchar(20))
Returns Table
as
Return 
(Select Id, Name, Gender, DepartmentId
from tblEmployee where Gender = @Gender)

Calling the function

Select * from dbo.fnEmployeeDetails('Male')

2. Rules and Defaults cannot be associated with views.

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

Code:

Create View vWEmployeeDetailsSorted
as
Select Id, Name, Gender, DepartmentId
from tblEmployee
order by Id

If you use ORDER BY, you will get an error stating - 'The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.'

4. Views cannot be based on temporary tables.

Insert Data

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

Insert into ##TempTable values(101, 'Ali', 'Male')
Insert into ##TempTable values(102, 'Ram', 'Female')
Insert into ##TempTable values(103, 'Shyam', 'Female')
Insert into ##TempTable values(104, 'James', 'Male')

Cannot create a view on Temp Tables

-- Error: Cannot create a view on Temp Tables
Create View vwOnTempTable
as
Select Id, Name, Gender
from ##TestTempTable