Best Practices for Table Indexing with Microsoft SQL

Whenever you have a large scale database with multiple tables and numerous requests from users, building tables with perfect indexing can be the difference between great performance or angry users with time outs. Table indexing is often ignored while designing an application. It is usually left to the database administrators to pick up the pieces after an application is promoted and poor indexing causes performance issues. As a great developer, you can avoid the pitfalls of poor table indexing and create a tuned application even on the database backend.

Primary Keys

As a developer, you have probably made the simple mistake of forgetting primary keys. If your applications and promotions go through a database administrator, it will be the first design flaw that will send your application straight to rejection. Primary keys can be generic, numerical incremented numbers for the first field, or you can use unique information within the table. The primary key must be original, unique, and it should not be long text or comments. Using illogical primary keys can actually slow down system performance.

Numerical Values for Dates

An obvious unique value for each record is the date when used down to milliseconds (if your database has that high amount of request volume). A primary key index can then be recorded through use of converting a date into a number. For instance, if a record is inserted on 6-20-2008 12:23:34.000 then you can rearrange this value to have perfect use for an indexed record. Using 20082006 as an index is also easy to read when spot checking data results from queries.

Use Clustered Indexes

For each table you design, you can have one clustered index. A table with a clustered index is physically sorted on that index in Microsoft SQL. When doing queries with an ORDER BY clause on the clustered index, will already have sorted information which will bring a significantly higher performance value for your database search results. Joining tables within queries on clustered indexes also is extremely fast. Most table designs use the primary key as a clustered index, because both values need to be unique, however it is not always feasible.

Defragment Indexes

Microsoft SQL 2005 supports online defragmentation your tables’ indexes. It is important for performance to defragment the indexes periodically as part of a maintenance schedule. Rebuilding and defragmenting indexes takes a high load of system performance especially within large databases, so it should only be done during off-peak hours or downtime.