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.
Denny
10 Responses
hey mrdenny,
i’m a fresher in database. could you please let me know more about index. You have stated that when we use index, the values are already sorted. so while search, it will go to the correct location. which search does this DB use basically?
Indexes are use to decrease the amount of time it takes to search a table. An index is a sorted copy of one or more columns of the table. When you search against a table using the WHERE clause it will use an index when possible, if not it will search the entire table.
Very clear picture and easy to understand. Fully Impressed. Thank u….
absolutely agree, simple and clear
hello Denny, if in one column there are 3 same values present, then the index value is also same?? please reply soon.
sangita90,
If you have an index and three rows have the same value, then yes the value would be in the index three times. If you have a primary key you can’t have three rows with the same value in the key columns.
Denny
Actually you dodged the question. What people really want to know: Is primary key already implemented internally as an unique index or do we have to add another index on primary key in case searches involve primary key in the where clause.
The Primary Key already has a unique index on it. If the query has the PK in the where clause it will use the PK to find the row.