Creating and Managing SQL Server Indexes
One of the main routes to top performance in a SQL Server Database is the index. Indexes make the querying process quicker by giving speedy access to rows in data tables, in the same way that an index in a book quickly helps you find information within the book.
Database indexes are comprised of a look-up value, i.e: a number identifier that corresponds to the row number in a table. There are 2 kinds of indexes in SQL Server - clustered and non-clustered. In clustered indexes it is required that the data in the table is sorted physically in the order of the index. Since the data in the table can only be physically sorted one way, there can only be one clustered index at most per table. With non-clustered indexes, it is not necessary for the data to be physically sorted, which means that there can be more than one non-clustered index per table. In fact the SQL Server allows for up to 249 non-clustered indexes per table. Due to the fact that the data is not physically sorted, the range searches with a non-clustered index do not tend to be very efficient.
You can use a specific command to create an index, or they can be created and managed using the Enterprise manager. You can create them using the Create Index Wizard from the Database Diagram or by modifying certain fields in the Table Designer.
There is a trade-off with indexes: whilst they immensely speed up the execution of queries, there is also associated overhead. They take up additional disk space and need more time to update themselves whenever data is updated or appended to a table.
When you are loading a large amount of data, it may be of benefit to drop the index before loading, then start the index afresh after the new records have been appended to the table. Indexes can be dropped using either the DROP INDEX command or the Table Designed.
Indexes can also become fragmented. To defragment an index, either drop then recreate the index or issue the command DBCC INDEXDEFRAG.
Picture courtesy of www.simple-talk.com