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

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 have recently wrote 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.

I think this list can be used as quick reference for anybody who wants to create view and index it to get best out of the views.

  • ANY, NOT ANY
  • Arithmetic on imprecise (float, real) values
  • COMPUTE, COMPUTE BY
  • 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 can not utilize the full potentials of the views.

Views have many limitations and the list is limitless!

Reference : Pinal Dave (http://blog.SQLAuthority.com), MSDN Article

About these ads

4 thoughts on “SQL SERVER – Keywords View Definition Must Not Contain for Indexed View – Limitation of the View 10

  1. Pingback: SQL SERVER – Indexed Views and Restrictions – Quiz – Puzzle – 7 of 31 « SQL Server Journey with SQL Authority

  2. Pingback: SQL SERVER – Weekly Series – Memory Lane – #008 « SQL Server Journey with SQL Authority

  3. Pingback: SQL SERVER – Weekly Series – Memory Lane – #048 | Journey to SQL Authority with Pinal Dave

  4. Pingback: SQL SERVER – Weekly Series – Memory Lane – #049 | Journey to SQL Authority with Pinal Dave

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s