Home Forums SQL Server 2012 SQL Server 2012 - T-SQL If I understand Views VS Functions correctly...I should use views to enapsulated commonly used where clauses? RE: If I understand Views VS Functions correctly...I should use views to enapsulated commonly used where clauses?

  • Regardless of the exact definition of Midwest states, I'm extremely adverse to nesting views within views, or functions within views or functions within functions.

    I recognize that creating a view means never having to figure out some difficult set of joins a second time, so that makes it extremely attractive to report writers. They have what they want in a single location and don't have to figure out the T-SQL to regenerate it.

    But...

    When you reference a view, the optimizer is forced to deal with every single object in that view. It determines which of them you are referencing, which of them it has to use to get your result, which of them are not necessary. That's all additional load on the optimizer. Throw in joining a view to a view, or calling a view from a view, and that load increases radically. What happens is that the optimizer can only try so many times to get you a good execution plan and then it gives up and goes with what it has. This is referred to as a timeout. If you're getting lots of timeouts on execution plans, you're probably also seeing really poor performance, or at least, very inconsistent performance as it finds different plans on different days.

    While it is more difficult, I'd suggest writing T-SQL that's unique to each report. That will work better with the optimizer.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning