SQL SERVER – Keywords View Definition Must Not Contain for Indexed View – Limitation of the View 10

SQL
4 Comments

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.

SQL SERVER - Keywords View Definition Must Not Contain for Indexed View - Limitation of the View 10 limitations

Update: Please read the summary post of all the 11 Limitations of the view SQL SERVER – The Limitations of the Views – Eleven and more…

Solarwinds

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

Solarwinds
, , ,
Previous Post
SQL SERVER – SELF JOIN Not Allowed in Indexed View – Limitation of the View 9
Next Post
SQLAuthority News – SQL Health Check and SQL Seminars

Related Posts

Leave a Reply

Menu