Codechef4u is a community for computer professionals,by computer professionals,just like you; who loves sharing and helping each others,Join them
Share your post

Limitations of the sql server view

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

 

Invalid entry,please enter valid data.

Loading