Precalculated SQL Views data

Question: I have a SQL View returning data .The SQL Server View is not schema bound. Normally users do a SELECT col1,col2,col3 FROM MYVIEW   . The data sets have grown and the users want to start filtering data , such as SELECT col1,col2,col3 FROM MYVIEW WHERE COL1 > xxxx.

If the users filter data on the SQL data , will all the data be returned via the VIEW and then filtered or will just the filtered data be returned?

Answer: A View is a pre-defined query. The Microsoft documentation  defines a View as “virtual table whose contents are defined by a query”. A common misunderstanding  about Views, is pre-calculation.  A View is not automatically pre-calculated.

When the SQL Server Query Optimizer is estimating the paths to use, it extracts from the View the columns, JOINS and other predicates required to work with the consuming query. Only when it has that information does it compile and execute the code.  Within this broad statement , there are a number of important details which need to be considered

Although  precalculation strictly doesn’t occur , there are some Query Optimizer subtleties requiring attention

A typical usage of Views is to disguise complexity from the Developers. The Developer can create a consuming query –  referencing the query within the consumer query. When SQL Server is trying to merge the data into the outer query and there is a mismatch between the cosnsumer query and the underlying indexes  (referenced by View), a precalculation may be required to Sort data

Form a SQL Performance perspective  creating consumer queries requiring  only require a small subset of the data offered through the View causes performance issues. There may multiple INNER JOINS, that are not essential.  Identify the common access patterns and build the View to accommodate these access patterns. Limit the usage of the View to these access patterns.

My general approach for SQL Server performance Views tuning is to treat them like any other query. Improve your troubleshooting skills by familiarising yourself with  SQL Indexed View limitations

SQL Performance tuning - Asking the right question

Read More on Slow running queries and troubleshooting

SQL Server – How to troubleshoot a slow running query

SQL Server – Find high impact queries with sys.dm_exec_query_stats

SQL Server – Write everything down during problem troubleshooting

How to report a SQL Server performance problem