I try to avoid join and query hints in T-SQL.

Adding a hint to a query is like buying a tailored suit. As long as your body doesn’t change - the suit will look great, but a little variation in your weight can turn a great suit into a disaster. Likewise, a query hint might boost the performance of a query on today’s data – but what about next week’s (or the next client’s) data? A hint might handcuff and prevent the query optimizer from formulating a better query plan.

In my experience - join hints, index hints, and FORCE ORDER hints appear when SQL Server doesn’t understand the data as well as the person writing the query. To avoid hints, try to ‘describe’ the data to SQL Server using indexes and statistics. Also make sure to apply UNQIUE constraints on any field that will contain unique values. Proper database design will go a long way to avoiding the need for join hints.

Non-clustered indexes in SQL Server contain only the values for the columns they index, and then a ‘pointer’ to the row with the rest of the data. This is similar to the index in a book: the index contains only the key word, and a page reference you can turn to for the rest of the information. Generally, the database will have to follow pointers from the index to other pages (known as bookmark lookups) to gather all the information required for a query.

If, however, the index contains all of the columns requested in a query, the savings in disk IO can be tremendous - the query engine doesn’t have to follow pointers around and seek out other pages on disk. We call these types of queries covered queries, because all of the columns needed for the query are covered by the index.

Covering a query with an index can be a significant performance boost, and in the BI applications I work with we often use this optimization. The caveat to this optimization is that the larger the index key, the more work the database has to do to compare keys. Measurements must be taken to ensure the proper balance. Also, index keys are limited to a maximum size of 900 bytes and 16 columns in SQL Server.

SQL 2005 introduces a nice little feature: included columns. Included columns will allow us to pack data values into a non-clustered index without increasing the key size of the index. Included columns also don’t count towards the 900 byte key limit or the 16 maximum columns. Example:

The above index has a small key size (just the PostalCode column), but will be able to cover any query using the other address fields. We will still pay a price in disk space, but it is now possible to design more effecient non-clustered indexes in SQL 2005.

The day after VSLive! wrapped up I stood outside the casino waiting for a shuttle bus to the airport. Unfortunately, the bus drivers seemed mostly grumpy that day and unwilling to cooperate, so another guest and I decided to split the cost of a cab.

The cab driver, a tall man with a cowboy hat, lifted our bags into the trunk while the other gentleman and I exchanged the typical pleasantries: where we were from, where we were going, and why we were in Las Vegas.

When the cab driver heard I was in software development he asked me if I ever developed any websites. “Yes”, I replied, “I’ve done a few websites”. Then the driver asked what database I had the most experience with. “Mostly Microsoft SQL Server”, I replied, “although some Oracle, too”. The next question was: “What technology do you use to write your web sites?” “Visual Studio and ASP.NET”, I say. “Really?” he says, “I like .NET”.

By this time I’m beginning to suspect I’m being driven by a software developer moonlighting as a cab driver, but before I get to ask he drops a loaded question: “What language do you use – C# or VB.NET?”

Now, my co-fare had no idea where the conversation was going – he was in town for some kitchen appliance convention, but I shot him a glance that said – “I hope you got my back – because this may get ugly”. We all know discussions about language preference are rarely civilized.

“C#”, I announced.

C# was an OK answer I guess, because we went on to talk about host headers, tablet PCs, and smart phones with 3GB hard drives inside. It turns out this fellow was a taxi driver who wants to start a website with forms for other drivers.

This would have been one of the best conversations I’ve ever had in a cab, except the automatic transmission was about one shift away from puking out gears and engine parts onto the road. Each time the car went to shift, there was breathless second of hesitation, followed by a head snapping lurch forward.

When it came time to pay up, I gave the driver a pretty good tip. I told him to download Visual Web Developer Express and jump into development with version 2.0!

A good place to see the new ReportViewer control in action is in the Web Log Analyzer Starter Kit for Visual Studio Beta 2. This news is via Brian Welcker’s new blog: Direct Reports. Brian is a Group PM for Reporting Services, and the blog is already trove of tips and information.

I’m on the last stop of “the tour” in Las Vegas, Nevada. There are two conferences in town – VSLive! and MEDC. I’ll be presenting twice at VSLive!, and them I’m going to relax with an open schedule for the foreseeable future. Hopefully I’ll get back to doing more writing in the evenings.

Here is some old news I’m just catching up on….

Reporting Services Service Pack 2 came out a few weeks ago. One anticipated feature was the client side print capability which adds a print icon to the report toolbar. Behind the little icon is a downloadable ActiveX control that will allow the user to preview and set print orientation for reports – without using Internet Explorer’s report mangling “print” features.

Also new, and report related, are the stand alone report controls in VS 2005 Beta 2. A new project item type Report (a file with an RDLC extension) is now available to add to your projects. Everything works very similar to the report designer included with the Reporting Services server product – but these controls don’t require a server. The only dependency is the .NET Framework 2.0.

There is also be a ReportViewer component in the toolbar that you can drag onto a Web or Win Form. You can point the viewer to a server based report on a report server, or to a local RDLC file in the same project.

Rajeev Karunakaran [MSFT] has written up a useful FAQ on the new components at sqltalk.org.

I had a great deal of fun presenting at the Code Camp today, even though one of my Virtual PC’s flaked out and I had to improvise a great deal. Argh!

The entire IIS / Visual Studio .NET coupling cannot die soon enough for me.

Congrats to G. Andrew D, the MS staff, all the volunteers, and all the speakers for putting on a great camp.

Here’s a quick pic of Bill Ryan, Sahil Malik, Wally McClure and Julie Lerhman hanging out by the mobile devices. Hopefully it's legible - the sunlight coming in the window killed my camera

.. it's uplifting to find kind words at the Inbox :

... I feel empowered now that the website modification is performing as I designed it. The instructions in Chapter 8 of your book have been my bible the last couple of months in my early a.m. efforts; and now they've paid off. I really feel that I understand the CSK. I wish you much success and entreat you to call upon me if I can somehow humbly be of help to your efforts ...

Blue skies to you too, Mike.