I'm an ASP.Net and SQL Server developer, not a DBA. Sure, I can write very complex select statements, stored procs and even SSIS packages that do lots of really cool stuff.   However, when it comes to the wheres and whys of database administration (tasks like creating and managing indexes), I'm a little out of my element...

...okay, a lot out of my element.

Today, I was looking at the schema for a table used in my current web app project; call it table "Clubs." Now, table Clubs only has about 400 records in it, but it does contain three "Officer" columns that reference a much larger table (table "Members" with 300,000+ records and counting). Since I inherited this database from multiple other solo contract developers spanning 10 years or so with little to know design consistency over that term, I was not surprised to see that the three Officer columns in table Clubs did not have foreign key relationships established with table Members.  Well, fixing that oversight is a no-brainer even for me.

After I created the foreign keys, I further noticed that indexes were not created for the foreign keys automatically. I just assumed that since you generally want an index for a foreign key to aid in query performance, that the indexes would be created automatically. Nope. Not with SQL Server, anyway.

So after a quick Google, and a brief stop at Stack Overflow (a useful post in and of itself), I was pointed towards this article on SQL Server Index practices by Diane McNurlan. Not only did it set me straight on the foreign key index issue mentioned above, but I gleaned several other interesting things to keep in mind when I find myself in the surrogate DBA role (a pretty common situation when a developer finds himself or herself working in small companies).

I won't go into all the gory details (a lot of it is, quite frankly, still over my head: Non-Sargable Where Clauses, WTF?!!). It's a short article. Check it out for yourself.

Thanks, Diane!