January 3, 2013 at 12:28 pm
I am looking for documentation on why a view chooses to organize joins the way it does. I can prepare an example but this is a pretty common thing to see. In TSQL I will organize my select a specific way starting from the Base table then getting the additional columns from additional tables. I look at that and think it looks great. :-D...Then I paste it into a view and suddenly my pretty select statement is gone and I can only understand what is going on by looking at the graphical interface. I just want to understand a bit more on why it does what it does and how to work with it better. I just want to be friends
January 3, 2013 at 12:32 pm
Are you using the GUI to build your view? I have not seen the behavior you are talking about. I created a view and when scripting it back out the query is exactly what I typed in originally.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 3, 2013 at 1:28 pm
Hey I script my views up (not using the GUI) and I don't ever get that problem.
January 3, 2013 at 3:02 pm
I think figured it out. the original query mostly had all Left Joins. When I change the linked tables as INNER the whole SQL portion of the view changes. I am not really sure how to explain this besides pasting in a before and after.
Here is the original
SELECT F.Active, F.FacilityName, F.URL, F.FacilitiesID, FacilityCountry.CountryName AS DefaultFacilityCountry, PA.Street, PA.Street2, PA.City, PA.State AS PostalState, PA.PostalCode,
PostalAddressCountry.CountryName, ASAddress.ListName AS PostalType, F.Note, F.BusinessesID, EA.EmailAddress, ASEmail.ListName AS EmailType, PN.DisplayPhone,
ASPhone.ListName AS PhoneType, F.FacilityType
FROM BusinessMgmt.Facilities AS F
LEFT OUTER JOIN BusinessMgmt.PhoneNumbers AS PN ON F.FacilitiesID = PN.ReferenceID AND PN.IsDefault = 1
LEFT OUTER JOIN BusinessMgmt.ApplicationSelectionLists AS ASPhone ON PN.SelectionListsID = ASPhone.ApplicationSelectionListsID
LEFT OUTER JOIN BusinessMgmt.PostalAddresses AS PA ON F.FacilitiesID = PA.ReferenceID AND PA.IsDefault = 1
LEFT OUTER JOIN BusinessMgmt.ApplicationSelectionLists AS ASAddress ON PA.SelectionListsID = ASAddress.ApplicationSelectionListsID
LEFT OUTER JOIN BusinessMgmt.Countries AS PostalAddressCountry ON PA.CountriesID = PostalAddressCountry.CountriesID
LEFT OUTER JOIN BusinessMgmt.Businesses AS B ON F.BusinessesID = B.BusinessesID
LEFT OUTER JOIN BusinessMgmt.Countries AS FacilityCountry ON F.CountriesID = FacilityCountry.CountriesID
LEFT OUTER JOIN BusinessMgmt.EmailAddresses AS EA ON F.FacilitiesID = EA.ReferenceID AND EA.IsDefault = 1
LEFT OUTER JOIN BusinessMgmt.ApplicationSelectionLists AS ASEmail ON EA.SelectionListsID = ASEmail.ApplicationSelectionListsID
then I put it in the view change them to inner joins and I get this result
SELECT F.Active, F.FacilityName, F.URL, F.FacilitiesID, FacilityCountry.CountryName AS DefaultFacilityCountry, PA.Street, PA.Street2, PA.City, PA.State AS PostalState,
PA.PostalCode, PostalAddressCountry.CountryName, ASAddress.ListName AS PostalType, F.Note, F.BusinessesID, EA.EmailAddress,
ASEmail.ListName AS EmailType, PN.DisplayPhone, ASPhone.ListName AS PhoneType, F.FacilityType, F.ExitMessage
FROM BusinessMgmt.ApplicationSelectionLists AS ASEmail
INNER JOIN BusinessMgmt.EmailAddresses AS EA ON ASEmail.ApplicationSelectionListsID = EA.SelectionListsID
RIGHT OUTER JOIN BusinessMgmt.ApplicationSelectionLists AS ASAddress
INNER JOIN BusinessMgmt.PostalAddresses AS PA ON ASAddress.ApplicationSelectionListsID = PA.SelectionListsID
RIGHT OUTER JOIN BusinessMgmt.ApplicationSelectionLists AS ASPhone
INNER JOIN BusinessMgmt.PhoneNumbers AS PN ON ASPhone.ApplicationSelectionListsID = PN.SelectionListsID
RIGHT OUTER JOIN BusinessMgmt.Facilities AS F
INNER JOIN BusinessMgmt.Businesses AS B ON F.BusinessesID = B.BusinessesID ON PN.ReferenceID = F.FacilitiesID AND PN.IsDefault = 1 ON
PA.ReferenceID = F.FacilitiesID AND PA.IsDefault = 1
LEFT OUTER JOIN BusinessMgmt.Countries AS PostalAddressCountry ON PA.CountriesID = PostalAddressCountry.CountriesID
LEFT OUTER JOIN BusinessMgmt.Countries AS FacilityCountry ON F.CountriesID = FacilityCountry.CountriesID ON EA.ReferenceID = F.FacilitiesID AND PA.IsDefault = 1
I think it automatically places the inner joins first and then it does this at the bottom which I don't really understand. i wish I could make a sample to depict what is happening but it only happens with complex queries it seems
January 3, 2013 at 3:14 pm
I am totally lost now...are you saying that it changed your inner joins to left and right joins?? You have me stumped.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 3, 2013 at 3:20 pm
No - I changed them to inner joins. The developer had everything as left joins. I did not want that since they are required fields. It then reformatted the tables in the sql portion of the view.
January 3, 2013 at 3:46 pm
I am assuming you are talking about using the Query Designer in SSMS to develop a View which accessed by right-clicking the Views node under a particular database and choosing New View...
The reformatting you are seeing is part of how the Query Designer functions. It does more actually than just reformat the select statement, it also exapands * when providing SELECT *as well as column-name correction (e.g. if it will change SELECT MemberID to SELECT MemberId if the column is actually named MemberId with a lower-case d).
If you want to maintain your formatting use a Query Window instead and write the CREATE VIEW DDL by hand.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
January 3, 2013 at 3:54 pm
opc. - So there is no way to really understand why it is reorganizing my tables from the original. Is it a black box? The join with the "is default" was particularly tricky because we had the left joins originally. I then corrected it with the inner joins. That changed the SQL. The developer then wanted to add isdefault = 1 in the join criteria. When I added the criteria the ui would not link the correct tables. I had to do it in TSQL then erase the view and start over. I wish I could show screen shots. It would be so much easier to explain.
January 3, 2013 at 4:54 pm
JKSQL (1/3/2013)
opc. - So there is no way to really understand why it is reorganizing my tables from the original. Is it a black box? The join with the "is default" was particularly tricky because we had the left joins originally. I then corrected it with the inner joins. That changed the SQL. The developer then wanted to add isdefault = 1 in the join criteria. When I added the criteria the ui would not link the correct tables. I had to do it in TSQL then erase the view and start over. I wish I could show screen shots. It would be so much easier to explain.
The query engine does not by default respect the order or format of the JOIN clauses (you can change that with a query hint, but its not a good idea in the general case so I'm ignoring its existence). While the Query Designer may change the formatting, it is not supposed to change the logical meaning of the query. If it is changing the logical meaning of your query then it is due to a bug in the Query Designer and you could report it to Microsoft.
Regardless of the formatting of the text of the query the query engine will optimize it depending on however it decides it can have the best chance of finding and returning the data for you the fastest, i.e. the format of the text in the FROM clause has no bearing on the logical meaning of the query.
If you care about formatting, which of course is important for readability and maintainability, then do not use the Query Designer, use a Query Window and write the CREATE VIEW by hand.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply