Support us .Net Basics C# SQL ASP.NET ADO.NET MVC Slides C# Programs Subscribe Buy DVD

Limitations of views - Part 42

Suggested SQL Server Videos before watching this Video
Part 39 - View basics
Part 40 - Updateable views
Part 41 - Indexed views

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

We will use tblEmployee table for our examples. SQL Script to create tblEmployee table:
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,'John', 5000, 'Male', 3)
Insert into tblEmployee values (2,'Mike', 3400, 'Male', 2)
Insert into tblEmployee values (3,'Pam', 6000, 'Female', 1)
Insert into tblEmployee values (4,'Todd', 4800, 'Male', 4)
Insert into tblEmployee values (5,'Sara', 3200, 'Female', 1)
Insert into tblEmployee values (6,'Ben', 4800, 'Male', 3)

Employee Table

-- Error : Cannot pass Parameters to Views
Create View vWEmployeeDetails
@Gender nvarchar(20)
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
(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.
Create View vWEmployeeDetailsSorted
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.

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

Insert into ##TestTempTable values(101, 'Martin', 'Male')
Insert into ##TestTempTable values(102, 'Joe', 'Female')
Insert into ##TestTempTable values(103, 'Pam', 'Female')
Insert into ##TestTempTable values(104, 'James', 'Male')

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

No comments:

Post a Comment

If you like this website, please share with your friends on facebook and Google+ and recommend us on google using the g+1 button on the top right hand corner.