Bill Talada (9/10/2012)
Good article. I just want to present a style of coding that prevents this situation. I often work with queries involving a dozen tables. Notice that each table is basically a separate select statement. If I didn't code in my style, I'd quickly get confused.
select
p.*,
c.*
FROM
(-- professor data
SELECT
Professor.Id AS [Id],
Professor.ProfessorName,
CASE Professor.HasTenure WHEN 1 THEN 'True' WHEN 0 THEN 'False' ELSE NULL END AS [Has Tenure]
FROM Professor
WHERE Professor.HasTenure = 'True'
) as P
left outer join
(-- class data
SELECT
Class.ProfessorId AS [ProfessorId],
Class.ClassName,
Class.ClassYear,
Class.ClassSemester
FROM Class
WHERE Class.ClassYear >= 2011
) as c on p.Id = c.ProfessorId
Interestingly enough one of my coworkers suggested exactly the same thing. I tried it while I was writing the article but it came up significantly slower than doing it the other way. Not noticeable with small data sets but if you turn STATISTICS TIME & IO on you can see it.
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]