SQL Server MVP Deep Dives 2, Chapter 2
After realizing that the chapter title was based on a quote from Monty Python’s Life of Brian I was expecting a lot from chapter 2. I was not disappointed. It is perhaps not quite as entertaining as hearing Rob give a PASS Lightning Talk about collation, but it was easy to read and I learned a lot. I learned random things like, a unique constraint shows up as a blue key in the object explorer. And I learned cool things like, combining a unique index with a filter to create unique pockets of data. I’m not sure when I would use that , but it is cool to know that I could. The example in the chapter is that you could enforce a rule stating all products which are red must have a different name, but there can be duplicate names among other colors.
Rob begins his discussion of uniqueness within SQL server by pointing out that it is so ingrained in how we think about a database that we don’t think about how important it is. He points out that even though you can create objects in SQL server that do not have a way to identify rows uniquely, they shouldn’t be called tables. He then outlines some of the differences between unique constraints and unique indexes. The largest being that constraints are logical and indexes are physical. He seemed to come close to admitting you don’t really need unique constraints if you use the indexes, but conceded they are good practice.
I get more of a chance to develop than design databases so the part of the chapter I really enjoyed was his comparison of DISTINCT and GROUP BY. I have heard to avoid Distinct when possible, but have never taken the time to find out why. Rob outlines concrete examples of how using Distinct can hurt query performance. I am in the middle of upgrading a project from SQL 2005 to SQL 2008 and as a part of this upgrade we are looking for any places to increase performance. As I reviewed the code I saw several places where distinct was being used. It didn’t make sense to change it in all instances, but in a couple of places I used a GROUP BY instead of the DISTINCT and I did see an increase in performance.
When I read the section about the bad habit some developers have regarding “that” error it was like Rob has been looking over my shoulder when I write ad hoc queries.
At this point, I want to break you of a common practice. When people write a GROUP BY clause, their behavior is driven by wanting to avoid that error we all know so well:
Msg 8120, Level 16, State 1, Line 1 Column 'Production.Product.Name' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
This is the error that occurs when the GROUP BY clause is ignored.
I have always hated this error and have complained loudly that if SQL Server is smart enough to know which columns I forgot to group by it can just add them behind the scenes. Mr. Farley pointed out that there are reasons to examine the GROUP BY clause and make sure it is grouping by not just the items I am selecting, but by the most unique level which makes sense for the data I am trying to retrieve. If you can group by the table’s indexed column and still retrieve the results you are looking for the performance gains could be significant. He outlines an example of grouping by a product name and switching to the product id. Product ID is not displayed in the result set, but using the table’s primary key gives you a grouping by product with much better performance. I suppose it is possible you may have two products with the same name, but then you probably have other issues. Of course if we used a natural key of product name we wouldn’t have to worry about this.
Chapter Two SQL Server MVP:
Rob Farley (B|T) is the owner and principal consultant of LobsterPot Solutions Pty Ltd., an Australian-based company specializing in SQL Server and business intelligence and the first Australian company to be a Gold Competency Partner in the Microsoft Partner Network. He’s a Microsoft Certified Trainer and a regular conference presenter both around Australia and overseas. He heads up the Adelaide SQL Server User Group and has received the Microsoft MVP Award for SQL Server every year since 2006. Rob is a past branch executive committee member of the Australian Computer Society, and he’s recently been appointed to the PASS Board of Directors. He’s a dedicated husband and father, the author of two chapters in the first volume of SQL Server MVP Deep Dives, and was proud to be able to contribute again to the second volume.
If I have interested you in SQL Server MVP Deep Dives Volume 2 you can acquire your own unique copy at Manning Books. I don’t get anything out of it if you buy the book, but you might. [Or as luck would have it Manning Books offers this chapter as a teaser on their website.]