Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Stairway to Advanced T-SQL Level 4: Using Views to Simplify Your Query Expand / Collapse
Author
Message
Posted Thursday, February 20, 2014 11:50 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, June 6, 2014 2:06 PM
Points: 1,040, Visits: 277
Comments posted to this topic are about the item Stairway to Advanced T-SQL Level 4: Using Views to Simplify Your Query

Gregory A. Larsen, MVP

Need SQL Server Examples check out my website at http://www.sqlserverexamples.com
Post #1543836
Posted Wednesday, March 19, 2014 9:57 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, November 24, 2014 3:09 AM
Points: 194, Visits: 313
Thanks Gregory
Nice and straightforward, I read through quickly just to check there was nothing new to me.

I write this to applaud your inclusion of the review questions at the end, I have been seeing more of this and I really think it helps understanding and retention.
Post #1552688
Posted Wednesday, March 19, 2014 1:54 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 5:13 PM
Points: 456, Visits: 1,060
Something interesting would be the new challenge of keeping up with the many views that can now be created against a single table. Such as to display one set of columns to one department, another set to another, one for updating , another one with joins,...etc... Do you have a set naming convention that you use to distinguish the views at the least by what their purpose is? Thanks, and good article.
Post #1552786
Posted Thursday, March 20, 2014 6:29 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, June 20, 2014 2:33 PM
Points: 5, Visits: 94
There is a general misconception of the following statement that you make.

"In this case SQL Server will still have to join all the tables in the view to return the data from a single table"

In the "Enterprise" edition, the query optimizer is smart enough to know that the other tables are not needed and does NOT include them in the query plan. You can easily verify this by generating the "Actual Execution Plan", where you see that the unused tables ar not included in the plan.

With this in mind, generating complex views with many table joins can be defined once (efficiently and correctly) and then used specifying only the desired field.
For example.

CREATE VIEW [dbo].[vwOrderInfo] AS
SELECT <Fields from all tables>
FROM OrderItems itm
JOIN Products prd On prd.ProductId = itm.ProductID
JOIN Brands bra ON bra.BrandID = prd.BrandID
JOIN Orders ord ON ord.OrderID = itm.OrderID
JOIN Customers cus ON cus.CustomerID = ord.CustomerID
JOIN SalesReps rep ON rep.SaleRepID = cus.SalesRepID

IF I need to report on a count of Orders By SalesRep, I can query;

SELECT SalesRepName, COUNT(DISTINCT OrderID)
FROM [dbo].[vwOrderInfo]
GROUP BY SalesRepName

Which would only use the tables Orders, Customers, SalesReps

If I need to report the Total By Brand and product, I can query;

SELECT BrandName, ProductID, ProductName, SUM(ItemUnit)
FROM [dbo].[vwOrderInfo]
GROUP BY BrandName, ProductID, ProductName

Which would only use the tables OrderItems, Products and Brands

The benefit is that this on ONE VIEW can serve many query situations efficiently and using the best query plan
while hiding the specific internals of the joins. If the underlying table structures cjange and some of the joins have to be changed, only this ONE view would change.

This is a very UNKNOWN feature that many people have misunderstood or had the misconception for a long time (myself included).

I hope this is clear.
Jose Ostos
Post #1552978
Posted Thursday, March 20, 2014 10:02 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, December 10, 2014 8:48 AM
Points: 468, Visits: 391
As a general rule I do not like views and I discourage developers from using them. My position is based on experience back to SQL 6.5. I find that developers adopt Views as a crutch for every problem rather than a tool for specific problems.
As a system grows, morphs, and ages the number of, and nesting level of views tends to expand beyond any reasonable limit. The 6.5 system I mentioned had views nested more than 6 levels deep and often the lower level views had no where clause.
I have recently joined a team whose in house application suite also uses many views. Again, they are often nested several levels (I have found 3 levels) and typically have significant, undocumented business logic embedded in the column specs.
I have a list of "Why do you need that" questions I pose to developers. Select Distinct and Views are near the top of the list.

BTW, IMHO Code Reuse is not sufficient justification for choosing a tool with so much potential for trouble.
Post #1553109
Posted Thursday, March 20, 2014 3:06 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, June 20, 2014 2:33 PM
Points: 5, Visits: 94
Like you, I did not like views for many years until I really knew what was going on under the hood and confirmed the the benefits for which they'r inteded. A lot has changed since SQL 6.5 so the topic deserves a seconf look into how it works now.

What you're describing has more to do with improper use of views by developers than the views themselves. Nesting views is a BAD PRACTICE and should not be done period.

Views should be looked at as "templates" that will tipically include all the possible fields and joins that could be related with the inderlying data. If you include everything in the complex view, there is no need for nesting, it's all included to begin with.

For example, let's assume that I don't want to use views. I could use the QUERY of the example view above as a starting poit, or as a "template". If I were to create a query for any of the scenarios I mentioned earlier, I could copy the code of the full query of the view and then ELIMINATE the fields and the JOINS that weren't necessary and end up with an efficient query that's properly done and no excess baggage.

Internally, that's EXACTLY what the query optimizer does giving the EXACT SAME result without having to copy and paste and most importantly, if this is done in multiple stored procedures, I don't need to repeat it multiple time. This becomes much more important in "Maintaining" the code later on.

I don't look at it as code reuse, I look at it as avoiding doing the same thing over and over again.
Post #1553250
Posted Monday, March 24, 2014 8:02 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, December 10, 2014 8:48 AM
Points: 468, Visits: 391
Hi Jay,
It seems that I did not get my point across. I completely agree that Nested Views are a BAD THING. And I understand your comments about templates, etc. I guess we could debate the semantics of "reusable code" through several rounds at a good watering hole.

My point is that application developers tend to think in coding terms. Functions, methods, routines, loops, sequences, etc. They do not tend to think in terms of sets and set operations. I have had conversations with developers at several organizations including internal and third party. Most of them see no difference between creating a nested view and inheriting or overloading a method. And they have often and explicitly played the code reuse card to support their coding.

Triggers, Views, User Defined Functions, SQLCLR, etc. are all powerful tools that can contribute to the solution when used properly. Unfortunately many application coders (VB, C#, etc.) jump on one of these tools an apply it to all problems.

I did not mention it in my original reply but I have seen (and am dealing with) extensive use of triggers to enforce referential integrity and I have found triggers referencing nested views.

I think the problem is as much caused by inexperience and the pace of development and change. I made the same mistake when table variables were first introduced. I thought they were THE SOLUTION and immediately began removing classic temporary tables from all of my scripts. It did not take long to find out that it was not such a good idea in all cases. Yet today, after nearly 10 years I still encounter developers who are convinced that Table Variables are a better choice in ALL CASES.
Post #1554034
Posted Thursday, April 3, 2014 4:11 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Yesterday @ 12:57 PM
Points: 21, Visits: 239
Also as a post done here about 4-5 months ago they cover partitioned views, which as Jay posted contradicts the post you have done about querie all tables on the views, also I think you overlook the encryption of views as a feature.
Post #1558292
Posted Friday, April 4, 2014 8:19 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, December 10, 2014 8:48 AM
Points: 468, Visits: 391
enriarg (4/3/2014)
Also as a post done here about 4-5 months ago they cover partitioned views, which as Jay posted contradicts the post you have done about querie all tables on the views, also I think you overlook the encryption of views as a feature.


Again, I did not say views are not a useful tool. I am currently implementing partitioned views. I also have created triggers and I use Cursors (a lot). I use Temp Tables, Table Variables, and any number of other specialized tools. I run SQL Profiler (both GUI and Scripted). I don't understand what there is in my comments that gives you the impression I am not aware of the capabilities and uses of views.

What I am saying, is that application developers (3GL and 4GL programmers VB, C#, C++) tend to jump on views and functions as quick, easy, Code ReUsing, solutions to problems. And then the views tend to proliferate and spawn new views. In that sense they are bad. And the disease is hard to cure once it is in place.

Here's another example. When I ask a developer why they used "Select Distinct" it is not because I don't think Distinct is a useful tool. It is because, over the years I have seen a number of very significant query logic, database design, data quality errors covered up by Select Distinct.

Post #1558528
Posted Saturday, April 5, 2014 3:39 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, June 20, 2014 2:33 PM
Points: 5, Visits: 94
I posted a reply ONLY to clear up a comment that was made in the original article, and mainly because most people actually consider the comment in the article as accurate, when in fact its not.

I know that there are many places where developers are allowed too much freedom to make database decisions which completely go against "Best Practices". Any object (Table, index, View, stored procedure, trigger etc.) that is implemented in a database should at least be reviewed and authorized by a competent database developer before it's implemented in a database. Otherwise, the database will eventually experience the kinds of problems that Ray is mentioning.

I believe that the articles and forums should be to talk about the way things should be done right, following "Best Practices" and how the tools and techniques can help to improve when used properly. I think we have all seen plenty of "not so good" code and probably have done some ourselves before we discovered better ways to do things, its all part of the learning process and that's why we're here to begin with.
Post #1558793
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse