Archive for the 'Chapter 7' Category


Interview with Rob Farley

After the reviews last week about Rob Farley’s chapter in the Deep Dives book, we talked over email a bit. I had the chance to sit down and ask him a few questions that Jeremy and I came up with. I hope that you enjoy them. What amazes me the most is the fact that Rob did not start as a database guy, not only did he not start as a database guy, but actually chose to pass up the opportunity to take a database class. Another item that really grabbed me was how Rob hires consultants that he has, see the product is growing so large that few know everything about SQL Server to the skill of being able to learn new things is just as positive and know the basics.

How did you get started with SQL Server? What is the draw to SQL Server, why do you like the product so much?

Funnily enough, I skipped the databases subject at university. People said it was easy marks, but I was more interested in other things. The things that fascinated me in those days were Artificial Intelligence, Natural Language Processing, things like that. And I liked to think I didn’t need easy marks. During my Honor’s year, I was offered a job in a Melbourne consultancy, and they said I’d need to know about databases, giving me a book to read about Oracle’s SQLPlus. It just clicked, with many of the principles relating to stuff I’d learned in my Pure Maths half-major – Set Theory, Number Theory, Logic.


So my first databases were Oracle, but I also had clients who used SQL Server. That was 6.0 back then. I was one of those consultants who seemed suited to being spread across many clients, sometimes working for six different clients in single day. Always some Oracle, but an increasing number of SQL Server ones as versions 6.5 and 7.0 were increasing in popularity in the late 90s.


All the while I was also writing code in iterative languages such as VB (versions 3 through 6) and C++, and didn’t consider myself a database guy. Everyone in the company did databases as part of what they did, and I didn’t appreciate it as a discipline of its own.


Years later, after having my career saw me venture into management and a continued generalization, I was still finding myself as the ‘SQL Expert’ in the company, and decided to specialize. So these days I do very little iterative programming, and spend almost all my time in SQL Server. I am very comfortable with the set-based paradigm, and I find this is probably the thing which continues to interest me – that difference between other IT people and the database guy.


Why did you pick the chapter or chapters that you wrote, what made that topic your topic of choice?

T-SQL has always had a special place for me. In consultancies of developers, it was my ability to see effective T-SQL solutions that differentiated me from my colleagues. I have written courseware on T-SQL, and consistently find that the gap between ‘competent’ and ‘expert’ is really large, but can often be addressed by an appreciation of the paradigm and a basic understanding of how to influence the decisions that the Query Optimizer can make.


So I wrote chapter seven about two main things.


One was a query that I came across that I suspected few people would really understand. It’s the main query from one of the Project Server Report Pack reports. I decided to describe a method for reading the FROM clause that would work regardless of how the query had been rearranged. After all, at some point we all get asked to fix a nasty query, and step one must be understanding it.


The other was about the fact that the Query Optimizer can ignore joins that aren’t used. This gives database professionals the opportunity to create views to encapsulate useful logic, while being confident that if an application doesn’t ask for all the columns, the QO will simplify it down a lot.


There are a bunch of other topics that I could’ve picked, but this seemed to make sense for one.


The other chapter I wrote was chapter forty, about indexes. In fact, in some ways it’s all the same topic, as all queries are really against indexes. I know we write them against tables, but really there’s no such thing as a table – they’re all just indexes (or heaps). If people had to write indexes instead of tables in their queries, the level of understanding would be very different.


But indexes aren’t just there for seeking and scanning. They also help the Query Optimizer decide how to run your query. Indexes can be used to constrain the system, so it’s easy to find ones that are “unused” according to the metadata about them (eg, sys.dm_db_index_usage_stats), but that still have a large influence over the performance of queries.



With respect to the quantity of new features being added into the SQL Server stack, have you found that the traditional DBA role is evolving in Australia?

Also, are you seeing new job titles based off that and are companies hiring managers learning about the new responsibilities and tasks with the broader stack that MSFT is delivering?

Certainly the Business Intelligence space has changed things, and is continuing to shift. A database professional is no longer just a DBA, responsible for backups and HA systems. I still find that few people consider themselves dedicated Database Developers, but the Business Intelligence Developer seems to be becoming a recognized role. I do wish that the industry recognized the need for dedicated database specialists much more than it does, as I keep finding that traditional programmers typically take the wrong approach to data. It gets more complex with things like LINQ, Hibernate, and other technologies, and even SQL features like StreamInsight, which I consider to be more of a .Net feature than SQL. The skills that a SQL expert has don’t automatically translate into BI, let alone some of these other features, which is making the SQL space far more complex than ever before. It’s a good space to be in, even if the days of being an expert across the whole platform are almost over.


I run a consultancy now, and in hiring people I need to look at their background, ability to learn, and where I can see them fitting into the overall platform. I can’t just hire a SQL guy any longer. I prefer to pick people based on their integrity more than any other feature though. I can always teach them how to think in a good way for MDX, or relational design patterns – integrity is the thing I want as a prerequisite.


Do you think there is a big culture difference between the US and AUS in the tech industry?

I actually don’t know what the US industry is like, but there are definitely differences between the UK and Australia. I grew up in the UK, and moved to Australia (Melbourne) as a teenager. I went back to London for a while, but returned to Australia (Adelaide) nearly eight years ago. I can assure you there are differences between Melbourne and Adelaide, let alone between Australia and the UK, so I’m sure there are big differences between here and the US.


What are your thoughts on technology debt in today’s rapidly changing environment with MSFT releasing major versions of SQL Server every 3 years now?


I think having regular releases is good, but of course people are so reliant on their database systems that few would mind if the data platform had less releases with a stronger stories around upgrading. Upgrading systems is still a major undertaking, and I think people often prefer to stay on older versions rather than risk their system for the sake of some additional functionality. Any time someone regrets an upgrade, the effort in improving the experience becomes that much harder for vendors.


I hope the people who are currently moving to SQL 2008 and 2008 R2 find it a positive experience, and that when version 11 comes around, the uptake is both larger and sooner.



Rob, it was great to talk with you, and I hope we have more opportunities to do more in the future.


Chapter 7 — Pulling Apart the From Clause

As I was writing this blog post this evening my three-year old daughter asked me what I was doing… I told her that I was working on “pulling apart the from clause”, she notified me that “I should only do that during the day time”.  It’s an apropos statement considering the light that Rob Farley sheds on one of the more fundamental and oft misunderstood element of t-sql.

The FROM clause is clearly necessary for most queries in t-sql.  One of the best talks that I’ve ever done was for the SSWUG v-Conference and I spent some time explaining the difference between Inner Join, Outer Join, Full Outer Join and Cross Joins.  The feedback that I received from that segment of the session was more than the other two sessions that I gave during that conference, combined.  It’s interesting that as I have been working with SQL Server for many years, I often lose sight of things that were difficult for me to learn early on.  The idea of combining an outer join, an inner join and yet another inner or outer join in one query was not easy for me to wrap my head around when I was new to relational databases.  So I fully appreciate the great work that Rob put together in this chapter.  It’s a very easy and fast read which is meant as a huge compliment to the time he spent thinking through how this subject could make the most sense.  Heck, I liked it so much, I’m going to lend the book to my Mom who is always asking me what I do and is getting tired of me trying to explain to her what I do. 

Back when SQL Server 2000 was in its prime I ran into a query that had over 120 join statements in it… The reason that it came to my attention was that it was so huge to compile that SQL Server (on 32 bit at the time) actually ran out of MEMORY while it attempted to create the query plan for it.  That was a fun excercise.  I learned a great deal about 32 bit memory architecture in the ensuing days and I also learned a great deal about SQL Server.  Upon opening a case with MSFT, they told me that there were two options for this beast of a query.  1.) Re-write it.  2.)  Buy new hardware that could support 64 bit, re-install the OS, SQL Server etc… and migrate the environment to 64 bit.  *sigh*.   It was a very long week but that query was re-written and in the process I learned how to eradicate unnecessary joins as Rob explains in the chapter.  More importantly I learned that even though it can be done, that doesn’t mean it should be done. 

Keeping that last thought in mind, what can be done, not what should be done, I offer one of the items that I’ve seen over the years, the usage of derived tables in join statements.  A quick example of this would be:

SELECT EMP.EmployeeID, Emp.FirstName, Emp.LastName
            (Select EMP2.FirstName, EMP2.LastName from Employee EMP2 Group By FirstName, LastName having count (*) > 1) EMP2
 ON EMP.FirstName = EMP2.FirstName AND EMP.LastName = EMP2.LastName

That query creates what’s known as a derived table (notice the Select statement in the From clause of the query).  This can be helpful at times when dealing with aggregations or as demonstrated above, finding those employees who either have the same name or are in the employee table twice. 

Thanks Rob for putting together a great chapter on the FROM clause.  While I don’t know the original author of the query that I had the joy of re-writing, I do hope that person knows that I had to re-write it and has taken the time to read your excellent chapter.


Pulling apart the FROM clause

Pulling apart the FROM clause…So in this latest chapter, the author(Rob Farley) goes into a lot of detail with the different types of JOINs and understanding the FROM clause.

When I first started reading the chapter, I immediately thought of a time where I was interviewing for a job and was asked to write out a simple JOIN query. I have to say first that I am not a good interviewee. I get too dang nervous and I have been told that before. Anyways, I was so nervous, I totally botched the query. I wrote it on the white board and when I stepped back to look at it, I thought “wow, that is way wrong”. I beat myself up (not literally) after that. Later I sent an email to the persons I interviewed with and thanked them and wrote out the correct way to write a JOIN query.

So back to the book. There are so many different ways to write out JOINS, its mind boggling. I enjoyed the section named “The appearance of most queries” because in that section I think there is an important piece that the author speaks about and that is “how to read a FROM clause”. If you are an experienced DBA, you may say “I know how to read a FROM clause”. That is fine but if you are a new DBA, learning to read and breaking down a complex query with billions of JOINs can really help in troubleshooting or just knowing where to start. This is another great section for experienced and new DBAs/ developers.

Thank you for sticking with us. Please leave us comments or sign-up to become a guest blogger. Have a good rest of the week.


Rob Farley FROM Down Under

I am one of those people that like to see and do more than just read. If I read something without testing it, playing with it, picking up the book, setting it back down again I just don’t retain it as well. This is just one of the reasons that I think the Pulling apart the FROM clause chapter is well written.

I will back up a bit. Rob Farley is a SQL Server MVP that is base out of Australia. He has a blog that can be found here: for the last month or so, but if you want to go back a bit more you may want to look here: From looking at the About page on his blog he has done work with more than just SQL Server.

The main idea of this chapter is to take a Deep Dive into the FROM clause, there is a strong attention to detail that Rob has when it comes to discussing the topic. There is information that starts with the FROM all by itself before you start to add any of the many layers to it. To the chapters end where he talks about how joins will impact you. When covering the many different angles such as the Joins or the Cross there are examples to help you see not only the syntax, but the proper usage.

This chapter is a must for anyone who writes SQL Code. There is so much here that I don’t believe many books compare to the depth of his coverage on the topic. The more that I think about it we should all spend some time in the topic, if there is an opportunity to improve our t-SQL skills I think we have to take it.


Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 13 other followers