Limitations and
restrictions of the sql server view
In this article I will explain limitations of using view in detail, following are some main restrictions
on view I listed with detail.
Definition:
View is a virtual table based on the result-set of an SQL
statement and that is Stored in the database with some name
Following are
some limitations with sql server views
1. SELF JOIN
is not possible with Indexed View
2. ORDER BY
now allowed with views.
Example:
Create View Orders_View
As
select OrderId,ProductId,Quantity,UnitPrice,Username FROM OrderDetail ORDER
BY OrderId desc
go
Error :
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.
3. Outer Join
Not Allowed in Indexed Views.
4. With
Index View union not allowed
5. You can't
create a view with a parameter or parameterized view.
Example:
Create View Orders_View
@OrderID int
As
SELECT OrderId,ProductId,Quantity,UnitPrice,Username FROM OrderDetail where
OrderId=@OrderID
Go
Error:
Must declare the scalar variable
"@OrderID".
6. Cross
Database Queries Not Allowed in Indexed View
7. You can’t
use keyword View Definition with Index View.
8. Views are
not based on temporary tables, when you try you will get error message like
Example:
CREATE TABLE ##ProductDetails(
ID int,
Product nvarchar(100),
Author nvarchar(100))
insert into
##ProductDetails values (1,'CodeChef4u e book','by NM Kendre')
View:
Create View Products_View
As
SELECT Id,Product FROM ##ProductDetails
Go
Error:
“Views or functions are not allowed on temporary
tables”