So you’ve designed your tables, then you set up your primary and foreign keys. You even defined views that your programmers will use to present the data to end users. What’s next? Well, if you want to take a proactive approach to database design, you’re going to want to start thinking about indexing.
Initial Indexes
When you start putting your views together you have an idea on how they’re going to be used. Let’s take our contact card view. We know that to produce the view we’re going to have to do a join between the Customer table and the phone number table (several times). We want to make sure that join performs as quickly as possible. So we’re going to want to index those key columns to ensure that performance.
Does everyone know the two primary types of indexes?
Clustered and Non-Clustered Indexes
Clustered indexes are the physical order of the data in the table. Since you’re physically ordering the data, you can only have one clustered index.
On the other hand, non-clustered indexes are written separately from the table, and are more like what you think of when you’re talking about an index in a book. It’s a list of references, along with a page number where you can find that term. Since non-clustered indexes are written separate from the table, you can have as many of these as you’d like (within reason!)
Since the non-clustered points back to the table, you’re going to want to have a clustered index on the table before defining any non-clustered indexes. You don’t have to have the clustered index first, but for performance, you want to try out any of your designs with the clustered index rather than trying it without. Trust me on this one.
Let’s get started designing some indexes
The good news is (unless you created your primary key in a non-default way) you’ve already built an index on the primary key of both tables. By default SQL Server sets your primary key to be the clustered index on the table. So you’ve already designed indexes!
If you had any tables where you don’t have a primary key defined, it can be tough judging which column or columns you want to set as your clustered index. In these cases I usually will wait to see queries being run against the table before attempting to define the clustered index. But there are some good rules of thumb for designing clustered indexes:
- You want to optimize your joins. If you’re joining to a table on a specific column a lot, and you don’t have a clustered index, that column may be a good candidate for a clustered index.
- You want to optimize your where clauses. If you include a column in a where clause quite often, and you don’t have a clustered index, and you don’t join against a certain column very often, you may have a good candidate for a clustered index.
Now, for non-clustered indexes
Let’s say we get word we’re going to have to add Address to vContactCard.
We know that we’re joining the Customer table to the Address table. We also know that we’ll join on CustomerID. Since that column is a foreign key in the Address table, and the address table already has a clustered index on AddressID, we have a candidate for a non-clustered index!
So in our design, we’re going to make index on CustomerID in the Address table.
If we wanted to speed up lookups in the Customer table, and we’re doing all our lookups by LastName, then we could add a non-clustered index on LastName in our Customer table.
Just don’t go crazy with indexing
Before you co adding non-clustered indexes on every column in your tables, know this: adding indexes can speed up reads, but they have a cost. They slow down your writes (inserts and updates). As a designer, you have to keep in mind this balance. That’s one of the reason I will often design my database solutions only defining the clustered indexes. I wait for testing and application development before adding non-clustered indexes.
I want the actual use of the tables to show me where the indexes are needed. It’s served me well so far.
Well, if you have any questions on indexes in database design, send them in…I’m here to help!