Indexes in general increase performance by reducing I/O. Without indexes, SQL Server would always have to read all of the rows in a table to find the subset of rows that have the values specified in joins or WHERE clauses. If you have indexes covering the columns referenced in the joins and WHERE clauses, SQL Server can use the indexes to read only the rows that match the query conditions. For example, say there is an Employee table with 100,000 employees, and you execute:

SELECT * FROM Employee WHERE EmpID = 12345

Without an index, SQL Server would have to scan through all the rows in the table looking for the one row that has the EmpID value 12345. On average, each query would have to read half the rows in the table, or 50,000 rows. If you have an index, SQL Server usually only has to read 1-3 index pages, and then the data page with the one row.

The main difference between a clustered and non-clustered index is that for a clustered index the data rows are sorted in sequence for the index key. The data rows are essentially the bottom level of the clustered index. For a non-clustered index, the data rows are not sorted in the index key sequence. The bottom level of the non-clustered index is a set of pointers to whatever rows match each key in the index.

To design indexes, you have to think through all the ways your application is going to access the data in a table; which columns in the table are going to be referenced in all the queries that reference the table. You then create indexes to cover the sets of columns that are going to be referenced in joins and WHERE clauses. Since you can only have one clustered index per table, you generally make the index that will satisfy most of the queries the clustered index, and all the others non-clustered indexes.