Views vrs Derived Tables

  • We have a ongoing debate about using views versus Derived Table Queries. Example of View would be:

    CREATE VIEW vw1 AS

    SELECT a, b, c

    FROM tbl1

    WHERE z = 1

    GO

    SELECT d, e, b_sum, c

    FROM tbl2 t2

    LEFT OUTER JOIN vw1 t1 ON t1.a = t2.a

    WHERE t1.a IS NULL

    Versus Derived Table:

    SELECT d, e, b, c

    FROM tbl2 t2

    LEFT OUTER JOIN ( SELECT a, b, c

    FROM tbl1

    WHERE z = 1)t1

    ON t1.a = t2.a

    WHERE t1.a IS NULL

    Now I know that this could have been written as:

    SELECT d, e, b, c

    FROM tbl2 t2

    LEFT OUTER JOIN tbl1 t2 ON t1.a = t2.a

    AND t2.z = 1

    WHERE t1.a IS NULL

    But that is not the point (I will be posting on this bug later...)

    The question is as follows:

    1. Which is easier to read?

    2. Which is easier to debug?

    3. Which is less likely to go wrong on a 8 way join?

    Your thoughts?

  • If the joins are always the same, a view would be the correction solution in my opinion. It think it meets all three of your criteria.

    Here's a question for you - what's a good reason for NOT using a view? Since you're in this debate, share the other side:-)

    Andy

  • Well, my opinion is the use of views for consistancy. I believe that like programming subroutines, this makes the job of debugging simpler and lends itself to more readable query definitions.

    However, it has been stated that this leads to deeply nested views (4 to 6 layers deep).

    Since security only exists on the topmost view (except in the case of a cross database query), there is no real security advantage to view of views.

    The other question is performance. I believe them to be the same in performance but a solid test of either of these has not been conclusive in our enviroment.

  • I dont think I'd sweat the nesting level. Its not going to get any simpler by making one big TSQL statement. If you could find a performance advantage then maybe.

    I disagree with you about security. Aside from permissions set on the views, you might be reducing the number of columns visible in the views - a form of security.

    To some extent it is about style. In my experience programmers seem to love the idea of stored procedures (equivalent to subroutines), but prefer to write TSQL over creating views.

    Andy

  • I've come across problems with deeply nested views where the server can take a long time to generate the plan (hours) and getting differring plans depending on which order things are first run in.


    Cursors never.
    DTS - only when needed and never to control.

  • I'd say risk the performance problem and deal with it when it happens.

    Andy

  • We use view extensively for this scenario. Often time we find that the subquery is needed in other areas. Also, you get the benefit of being able to alias the columns so their names represent their use in this particular subquery.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply