Posted by: Denny Cherry
There seams to be some confusion between what a Primary Key is, and what an Index is and how they are used.
The Primary Key is a logical object. By that I mean that is simply defines a set of properties on one column or a set of columns to require that the columns which make up the primary key are unique and that none of them are null. Because they are unique and not null, these values (or value if your primary key is a single column) can then be used to identify a single row in the table every time. In most if not all database platforms the Primary Key will have an index created on it.
An index on the other hand doesn’t define uniqueness. An index is used to more quickly find rows in the table based on the values which are part of the index. When you create an index within the database, you are creating a physical object which is being saved to disk. Using a table which holds employees as an example:
CREATE TABLE dbo.Employee (EmployeeId INT PRIMARY KEY, LastName VARCHAR(50), FirstName VARCHAR(50), DepartmentId INT, StartDate DATETIME, TermDate DATETIME, TermReason INT)
The EmployeeId is the Primary Key for our table as that is what we will use to uniquely identify an employee. If we were to search the table based on the last name the database would need to read the entire table from the disk into memory so that we can find the few employees that have the correct last name. Now if we create an index on the LastName column when we run the same query, the database only needs to load the index from the disk into memory, which will be much quicker, and instead of scanning through the entire table looking for matches, because the values in the index are already sorted the database engine can go to the correct location within the index and find the matching records very quickly.
Hopefully this will help sort out some of the confusion.