I had earlier written, interesting article series on the limitations of the views. I had a great time writing this series. I got many requests to write more on the subject, but honestly, I think I gave my best to the subject.
I am now interested to read what all of you have to offer on this subject. I encourage all of you to go through complete series and write your own on the subject. If you write an article and send it to me, I will publish it on this blog with due credit to you. If you write on your own blog, I will update this blog post pointing to your blog post.
Limitations of the Views
- SQL SERVER – ORDER BY Does Not Work – Limitation of the View 1
- SQL SERVER – Adding Column is Expensive by Joining Table Outside View – Limitation of the View 2
- SQL SERVER – Index Created on View not Used Often – Limitation of the View 3
- SQL SERVER – SELECT * and Adding Column Issue in View – Limitation of the View 4
- SQL SERVER – COUNT(*) Not Allowed but COUNT_BIG(*) Allowed – Limitation of the View 5
- SQL SERVER – UNION Not Allowed but OR Allowed in Index View – Limitation of the View 6
- SQL SERVER – Cross Database Queries Not Allowed in Indexed View – Limitation of the View 7
- SQL SERVER – Outer Join Not Allowed in Indexed Views – Limitation of the View 8
- SQL SERVER – SELF JOIN Not Allowed in Indexed View – Limitation of the View 9
- SQL SERVER – Keywords View Definition Must Not Contain for Indexed View – Limitation of the View 10
- SQL SERVER – View Over the View Not Possible with Index View – Limitations of the View 11
- SQL SERVER – Index Created on View not Used Often – Limitation of the View 12
Please leave a comment.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
Discover more from SQL Authority with Pinal Dave
Subscribe to get the latest posts sent to your email.
7 Comments. Leave new
Hey pinal here one more limitation of the Indexed View
I got the error while createing IV from synonyms.
Error : -Msg 2788, Level 16, State 1, Procedure U_ALLOBJECT, Line 4
Synonyms are invalid in a schemabound object or a constraint expression.
read all articles..informative. thanks!
GReat pinal sir…………………….
Most of this limitations are connected with indexed views.
can we use set like
SET ARITHABORT OFF
SET ANSI_WARNINGS OFF
in views ??
Which of the following statements regarding SQL Server 2012 objects is TRUE?
A. A user-defined data type can accept an input variable and return a table of results but cannot be used within a view.
B. A scalar function can accept an input variable and return a table of results but cannot be used within a view.
C. A table-valued function can accept an input variable and return a table of results but cannot be used within a view.
D. A table-valued type can accept an input variable and return a table of results but cannot be used within a view.
sir pls help to find the correct answer.
BTW: you can’t use a CTE in an indexed view (well you can create a view with it but not index it)