Microsoft® SQL Server™ 2008 All-In-One Desk Reference For Dummies®

VII.3.2. Using Indexes to Enhance Performance

A comprehensive indexing strategy, which is often overlooked by harried database designers and application developers, is often all that's needed to supercharge a sluggish database solution. It's common to see performance improve by orders of magnitude simply by creating a new index or correcting an erroneous one. In this section, we give you some concrete, easy-to-implement index-related suggestions that can have a dramatic impact on performance.

NOTE

Book II, Chapters 4 and 6 are focused on all things related to creating tables, including considerations regarding indexes.

VII.3.2.1. Always define a primary key

It's easiest to think of a primary key as a unique identifier that guarantees a row's individuality. In some cases, your data already contains one or more candidate columns for primary keys. In other situations, SQL Server can generate a primary key for you. For example, take a look at Figures 3-3 and 3-4.

In Figure 3-3, we created a table and defined a primary key on an existing column. In Figure 3-4, we used the combination of the uniqueidentifier data type and the NEWID() function (used when inserting new rows) to have SQL Server create a dedicated column that will hold a unique, binary key value. As an alternative approach, we could have chosen the identity property for the primary key column, and SQL Server would have added new values to that field as well.

Figure VII.3-3. A DBA-defined primary key.

The reason we ...