Whenever I teach about SQL Server performance tuning I try can get across the message that there is no such thing as a table. Does that sound odd, well it isn't, trust me. Rather than tables you need to consider structures. You have

1. Heaps

2. Indexes (b-trees)

Some people split indexes in two, clustered and non-clustered, this I feel confuses the situation as people associate clustered indexes with sorting, but don't associate non clustered indexes with sorting, this is wrong. Clustered and non-clustered indexes are the same b-tree structure(and even more so with SQL 2005) with the leaf pages sorted in a linked list according to the keys of the index.. The difference is that non clustered indexes include in their structure either, the clustered key(s), or the row identifier for the row in the table (see http://sqlblog.com/blogs/kalen_delaney/archive/2008/03/16/nonclustered-index-keys.aspx for more details). Beyond that they are the same, they have key columns which are stored on the root and intermediary pages, and included columns which are on the leaf level.

The reason this is important is that this is how the optimiser sees the world, this means it can use any of these structures to resolve your query. Even if your query only accesses one table, the optimiser can access multiple structures to get your results. One commonly sees this with a non-clustered index scan and then a key lookup (clustered index seek), but importantly it's not restricted to just using one non-clustered index and the clustered index or heap, and that's the challenge for the weekend.

So the challenge for the weekend is to produce the most complex single table query.

For those clever bods amongst you that are thinking, great I will just use lots of xquery functions, sorry these are the rules.

1. You have to use a table from AdventureWorks (2005 or 2008)

2. You can add whatever indexes you like, but you must document these

3. You cannot use XQuery, Spatial, HierarchyId, Full Text or any open rowset function.

4. You can only reference your table once, i..e a FROM clause with ONE table and no JOINs

5. No Sub queries.

The aim of this is to show how the optimiser can use multiple structures to build the results of a query and to also highlight why the optimiser is doing that. How many structures can you get the optimiser to use?

As an example create these two indexes on AdventureWorks2008

create index IX_Person_Person on Person.Person (lastName, FirstName,NameStyle,PersonType)

create index IX_Person_Person on Person.Person(BusinessentityId,ModifiedDate)with drop_existing

select lastName, ModifiedDate

  from Person.Person

 where LastName = 'Smith'

You will see that the optimiser has decided to not access the underlying clustered index of the table but to use two indexes above to resolve the query. This highlights how the optimiser considers all storage structures, clustered indexes, non clustered indexes and heaps when trying to resolve a query.

So are you up to the challenge for the weekend to produce the most complex single table query?

The prize is a pdf version of a popular SQL Server book, or a physical book if you live in the UK.