Creating Indexes for Performance in C#

Generate EAN13 in C# Creating Indexes for Performance

12

Indexes are really all about the I/O. You use indexes to decrease the number of I/O operations that you perform. When you perform a table scan, thousands or even millions of I/Os are generated. These operations are expensive. Using an index finds your data faster because there are fewer reads necessary in order to find your data. By performing fewer I/Os, performance is increased and resource utilization is reduced.

The index is created with one or more index keys. The index key is the column or columns in the table that define what is indexed. This is the value that will be used to find the data in the table quickly. It can be character strings, integers, floats, and so on. Because these keys are used as the criteria for finding the data in the table and because you don t always look for data using the same columns in your WHERE clause of your query, multiple indexes are allowed. The exception is the clustered index, which is discussed later in this chapter. You can have only one clustered index per table. Note The index key column does not support the following data types: image, ntext, and text data types. Since indexes are created on index keys, you must include the leading key values in the WHERE clause of your SQL statement in order to use an index. If you do not include the index key in the WHERE clause, that index won t be used to find your data. Specifically, the leading side of the index must be included in the WHERE clause of the SELECT statement. In addition, there are a few other restrictions that are described later in this chapter. An index that has been defined with only one key column is called a simple index. An index that has more that one key column is called a composite index. More than one column should be used in the index key if it makes the index more unique or gives it greater selectivity. The more unique the index, the better the index since it allows for fewer rows to be retrieved within the queries. As you will learn later in this chapter, indexes should be created to be as unique as possible, but very wide indexes with lots of key values are less efficient in terms of space and modification performance. Note

The benefit of an index is that you can find your data with as few reads as possible. The wider the index, the more index pages that it consumes, and thus it takes more space and more pages are needed to find the desired data. As a

result, there is always a give-and-take between creating more unique indexes and creating smaller indexes.

An index can be either unique or non-unique. With a unique index, there can be only one index key value; with a non-unique index, you can have duplicate index key values. For example, if the unique index were created on Lastname, Firstname, there could be only one entry for each name, a duplicate entry would be refused and an error issued. A unique index has the highest level of selectivity that an index can have, since each key value is associated with only one row, or each row has a unique key value. Any attempt to insert a duplicate index key value into a unique index will result in a failure. A set of columns that is designed to uniquely define each row in the table is called the Primary Key (PK), as mentioned in 13, Enforcing Data Integrity. The primary key is usually associated with a constraint and often is a very good candidate for a clustered index. Unfortunately, a table cannot always be defined with a primary key since it might be impossible to uniquely identify a row. There can be only one primary key per table, and the primary key cannot contain NULL values. When the primary key is defined, a unique index that is used to enforce uniqueness is created.