none
VIEWS and WHERE clause

    Question

  • I am sure this has been asked (and answered) before but for the life of me I cannot seem to find any posts...

     

    When does a WHERE clause against a view get applied if the underlying SELECT does not have a WHERE clause? To make this question clearer, follow this example:

     

    Say I have a table (call it [People]) that contains [Name], [Age], and [Birthday] columns. Assume we have 100 rows of data in it.

     

    And say I create a view (call it [vTenYearsOlder]) that returns everyone but adds 10 to their age. As in:

     

    SELECT [Name], [Age] + 10 AS [OlderAge], [Birthday] FROM [People]

     

    If I then perform a SELECT against the view looking for anybody with a birthday earlier than a specific date, as in

     

    SELECT [Name], [OlderAge] FROM [vTenYearsOlder] WHERE [Birthday] > '20000101'

     

    How does SQL handle this? Because the view is not using a WHERE clause in the underlying SELECT does it return all 100 rows (and perform the [Age] + 10 calculation) and THEN filter for the '20010101' birthday? Or is it smart enough to "merge" the queries and apply the WHERE clause earlier?

     

    Thanks,

    Jason

    Wednesday, May 30, 2007 6:03 PM

Answers

  • The definition of the view is always parsed into the main query and optimized as a whole. The query optimizer doesn't see the view as such. So it doesn't matter where you put the WHERE clause. In case of multiple predicates, the evaluation of any predicate depends on the execution plan. It can happen in many places like:
     
    1. Seeks or Scans
    2. Joins
    3. Filters
     
    In addition, SQL Server can also evaluate expressions in the SELECT list out of order or replace expressions with computed columns in tables or match certain parts of your queries to indexed views etc. There are many advanced optimization techniques depending on your schema. So don't worry about where a particular condition / predicate / expression gets evaluated. You should instead focus on the necessary logic or syntax to get the results. The optimizer will decide on the best strategy. Lastly, there are however cases where the query optimizer will not evaluate all strategies and hence may result in a bad plan. For such cases, you could break the query into multiple parts using temporary tables or use hints to get faster results.
    Wednesday, May 30, 2007 6:48 PM

All replies

  • Short answer, it's smart enough to apply the WHERE clause earlier.

     

    You may confirm this by some simple testing.  Write the query once using a view, and then exactly the same without the view.  They will perform very similarly.  This will re-assure you that SQL is smart with the WHERE (otherwise, the VIEW version of your query would be WAY slower).

     

     

     

     

    Wednesday, May 30, 2007 6:09 PM
  • It is, as you say, "smart enough to 'merge' the queries and apply the WHERE clause."
    Wednesday, May 30, 2007 6:09 PM
  • Hi Jason,

     

    T-SQL is no different than any other linear based language. I will read from top to bottom and from left to right.

     

    It reads SELECT, then fieldnames or calculations, then FROM, then execute VIEW if present, then applies the WHERE filter changing the resultset of the View.

     

    The final resultset is as you expect it to be. The WHERE filter applies to the View

     

    Adamus

     

     

    Wednesday, May 30, 2007 6:13 PM
  • The definition of the view is always parsed into the main query and optimized as a whole. The query optimizer doesn't see the view as such. So it doesn't matter where you put the WHERE clause. In case of multiple predicates, the evaluation of any predicate depends on the execution plan. It can happen in many places like:
     
    1. Seeks or Scans
    2. Joins
    3. Filters
     
    In addition, SQL Server can also evaluate expressions in the SELECT list out of order or replace expressions with computed columns in tables or match certain parts of your queries to indexed views etc. There are many advanced optimization techniques depending on your schema. So don't worry about where a particular condition / predicate / expression gets evaluated. You should instead focus on the necessary logic or syntax to get the results. The optimizer will decide on the best strategy. Lastly, there are however cases where the query optimizer will not evaluate all strategies and hence may result in a bad plan. For such cases, you could break the query into multiple parts using temporary tables or use hints to get faster results.
    Wednesday, May 30, 2007 6:48 PM