Chapter 13. Creating and Managing Indexes

by Paul Jensen


  • Types of Indexes

  • Creating Indexes Using T-SQL

  • Creating Indexes Using Enterprise Manager

  • Managing Indexes

  • Dropping Indexes

The previous chapter, "Creating and Managing Tables in SQL Server," discussed the creation of tables, a structure in which to store your data. The data in a table is stored in no particular order?in fact, it's referred to as a heap. To find a particular piece of data, the entire heap must be scanned. This is known as a full table scan, and it is analgous to this book having no index or table of contents. Every time you wanted to find information on tables, for instance, you would have to start at page 1 and flip through the entire book to make sure you retrieved all the pertinent information.

An index in a database is similar to the one in this book. An index is sorted based on a key value (topics in the book, perhaps an employee number in a table). Just as the index in a book has a page number to quickly locate a topic, the index has a row identifier to drill down to the exact location of a row in a table.

Indexes can greatly improve the speed of your queries, and are also used to enforce uniqueness in data, as in the case of Primary Key and Unique Constraints. Why not index everything? Indexes require additional storage space on disk, just as the index in this book required additional pages. If you were to index every word in the book, the index would be larger than the book! Another consideration is for inserts, updates, and deletes. Indexing columns with frequently changing data means that every change to the table requires a corresponding change to the index. This additional overhead would, of course, result in performance degradation.

This chapter introduces the two types of indexes used by SQL Server and the methods you can use to create and manage them. Further information on index use by constraints appears in Chapter 14, "Implementing Data Integrity," and a detailed discussion on index structure, performance, and design can be found in Chapter 34, "Indexes and Performance." The ability to create an index on a view, a new feature for SQL Server 2000, is covered in Chapter 27, "Creating and Managing Views in SQL Server." Of course, everything will be easy to find by using the index at the back of this book!

    Part III: SQL Server Administration
    Part IV: Transact-SQL
    Part V: SQL Server Internals and Performance Tuning
    Part VI: Additional SQL Server Features