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.