SQL Server has two basics kinds of indexes. They are clustered and nonclustered indexes. There are some fundamental differences to the two which are key to understanding before you can master index tuning.
First the things that they have in common.
Both clustered and nonclustered indexes can be made up of more than one column. The columns are put in the index in the order you specify them in the CREATE INDEX statement (or the order they are shown in the UI). They are also sorted in this order as well. Indexes are first sorted on the first column in the index, then any duplicates of the first column and sorted by the second column, etc. You can have up to 16 columns specified as indexed columns.
Neither clustered or nonclustered indexes will guarantee the sort order of the data when it is being returned to you. If the order of the data matters to you, you should always sort the data with the ORDER BY clause in your select statement.
Both clustered indexes, and nonclustered indexes take up additional disk space. The amount of space that they require will depend on the columns in the index, and the number of rows in the table. The clustered index will also grow as you add columns to the table (keep reading, it’ll make sense later on).
Adding indexes (both clustered and nonclusterd) will increase the amount of time that your INSERT, UPDATE and DELETE statement take, as the data has to be updated in the table as well as in each index. If you have filtered indexes in SQL Server 2008 and the records you are updating are not included in all your indexes, SQL Server should only have to update the values in the indexes which the records are stored within.
Columns of the TEXT, NTEXT and IMAGE data types can not be indexed using normal indexes. Columns of these data types can only be indexed with Full Text indexes.
If you wish to rebuild your indexes online (without locking the table) and have Enterprise edition do not index TEXT, NTEXT, IMAGE, VARCHAR(MAX), NVARCHAR(MAX), VARBINARY(MAX) data types as including columns with these data types will require that you rebuild the index offline.
The total size of the key columns can not exceed 900 bytes. Don’t forget that uni-code characters take up two bytes per character which will reduce the number of characters which your index can hold.