Professional Microsoft® SQL Server® 2008 Programming

Chapter 7. More Advanced Index Structures

Alright, so we've walked through the basics of design. Heck, we've even walked through the advanced stages of traditional indexing. There are, however, some even more advanced things to think about in indexing and other storage. Among these are some of the atypical index and storage structures including:

• XML indexes

• Spatial data and their associated indexes

• User-defined data types

• Filestreams

• Table compression

• Hierarchical data

In this chapter, we'll take a look at each of these. Some of it will build on things you already know (like the XML data type and methods we've already talked about extensively), and some will likely be totally new. (Indeed, the remaining items are new with SQL Server 2008.)

The choice to group these particular items into one chapter may seem a bit crazy (even to me), but the thing they have in common is pretty simple. They are all somewhat out of the mainstream and require a bit of extra thinking to see how they work.

XML Indexes

XML indexes first appeared in SQL Server 2005, and I have to admit that I continue to be mildly amazed that Microsoft pulled it off. I've known some of that team for a very long time now, and I have a lot of confidence in them, but the indexing of something as unstructured as XML has been a problem that many have tried to address, but few have done with any real success. Kudos to the SQL Server team for pulling this one off. Enough gushing though. I want to get down to the business of what XML indexes ...