I have recently written many articles on the limitation of the views. In this article, I have tried to sum up all the keywords which are not allowed in the indexed view. If any of the following keyword is used in the View, Index is not possible to create on the same.
Update: Please read the summary post of all the 11 Limitations of the view SQL SERVER – The Limitations of the Views – Eleven and more…
I think this list can be used as quick reference for anybody who wants to create a view and index it to get best out of the views.
- ANY, NOT ANY
- Arithmetic on imprecise (float, real) values
- COMPUTE, COMPUTE BY
- The Contradictions SQL Server can detect that mean the view would be empty (for example, where 0=1 and…)
- CONVERT producing an imprecise result
- COUNT(*)
- Derived tables (subquery in FROM list)
- DISTINCT
- EXISTS, NOT EXISTS
- Expressions on aggregate results (for example, SUM(x)+SUM(x))
- Full-text predicates (CONTAINS, FREETEXT, CONTAINSTABLE, FREETEXTTABLE)
- GROUP BY ALL
- Imprecise constants (for example, 2.34e5)
- Inline or table-valued functions
- MIN, MAX
- Nondeterministic expressions
- Non-Unicode collations
- OPENROWSET, OPENQUERY, OPENDATASOURCE
- OPENXML
- ORDER BY
- OUTER join
- References to a base table with a disabled clustered index
- References to a table or function in a different database
- References to another view
- ROWSET functions
- Self-joins
- STDEV, STDEVP, VAR, VARP, AVG
- Subqueries
- SUM on nullable expressions
- Table hints (for example, NOLOCK)
- text, ntext, image, filestream, or xml columns
- TOP
- UNION
After looking at the long list which contains Self Join, TOP, UNION, OUTER JOIN and many other useful keywords, one has to wonder how limited the usage of the query is in the view. One cannot utilize the full potential of the views.
Views have many limitations and the list is limitless! Please comment with your ideas below.
Reference: Pinal Dave (https://blog.sqlauthority.com), MSDN Article
