T-SQL Tuesday #007: My Favorite 2008 Feature

This post is my contribution to T-SQL Tuesday #007, hosted this month by the SQLChicken himself, Jorge Segarra (Blog | Twitter).

Since I'm a bad DBA and have yet to get my hands on a copy of R2, I'll make use of the rule that we can discuss any feature from 2008.  My favorite is filtered indexes, as they were the solution to an issue I had been trying to fix for 5 years.

The Background

In a former life, I spent several years working as a building inspector for one of Chicago's western suburbs.  It quickly became evident that they were in need of a system for keeping track of permits, inspections, and other day-to-day activities, as their gigantic spreadsheet clearly wasn't cutting it.  Wanting to learn about databases and see if I could solve said problem, I picked up a book on Access 2000 and my solution was born.  It's grown up a lot in the past 7 years, and is now used by 2 other municipalities as well.

The Problem

In my system, building permits can have one or two unique identifiers depending on their stage in the permit lifecycle.  That probably doesn't make sense, so I'll elaborate.  When a resident applies for a building permit, a record is created in the permit table with a primary key known as the PermitId.  This permit has a status of pending, as it has only been applied for and not issued.  Many permits never make it to the issued state, as they may be denied or their application withdrawn.  Permits that make it through the approval process and are issued receive a Permit Number, another unique identifier.  Enforcing uniqueness on the PermitNumber column was desired, but impossible as all pending permits had a NULL value for PermitNumber.  The best I could do for this (given my knowledge at the time) was to create a non-unique index on PermitNumber and hope that duplicate values didn't end up occurring.  While that may be good enough for some, it always irked me.

Some may ask why PermitId and PermitNumber can't just be combined.  The answer is that it's a business requirement.  All three of the cities I do business with issue PermitNumbers in sequential order based on the permit issue date, so there's no way the PermitNumber can be determined ahead of time.  Also the PermitId is an internal value and is only known to the database for use in joins.  PermitNumber, on the other hand, is known to the public and is commonly searched on, so indexing it is desirable.

The Solution

The Filtered Index feature allowed me to create a unique index on PermitNumber where the value is not null.  I was able to enforce uniqueness with permit numbers, and all was right in the world once again.  The syntax to do this was extremely simple:

1CREATE UNIQUE INDEX UQ_Permits_PermitNumber
2ON Permits(PermitNumber)
3WHERE PermitNumber IS NOT NULL; -- The WHERE statement makes it a filtered index

And there you have it, a simple solution using elegant syntax.  'Tis a shame that this feature didn't appear until 2008, but I'm sure glad it's there now!