October 12, 2001 at 9:22 pm
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?
October 13, 2001 at 6:33 am
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
October 13, 2001 at 8:34 am
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.
October 13, 2001 at 2:43 pm
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
October 30, 2001 at 2:35 pm
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.
October 30, 2001 at 3:10 pm
I'd say risk the performance problem and deal with it when it happens.
Andy
November 5, 2001 at 9:04 am
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