Indexes are used to decrease the amount of time to query the table.
Assume that you have a table setup like this.
CREATE TABLE Emp
(EmpId INT PRIMARY KEY,
If you were going to often query based by the DepartmentId you would want an index on the DepartmentId column. An index is a separate copy of the data from the table, sorted on the indexed column. So in this case it would be sorted by the DepartmentId. This allows the SQL Server to more quickly find the records for the department that you are looking for.
So assuming that you are looking for all employees in Department #1 you would run this query.
WHERE DepartmentId = 1
Without an index on the DepartmentId column the SQL Server would need to scan the Emp table looking for records with a value of 1 in the departmentId column. With an index on the DepartmentId column the SQL Server knows where the first record is that has a value of 1, and it reads the records until it comes to a record with a different value (as they are stored in order). It then returns these records. This operation is called an index sek.
You may want to check out one of the presentations I’m giving at this weekends SoCal Code Camp one of which is on indexes.