SQL Server with Mr. Denny

Aug 31 2009   6:26PM GMT

Difference between an Index and a Primary Key

Denny Cherry Denny Cherry Profile: 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.

Denny

6  Comments on this Post

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • RaghuCD
    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?
    0 pointsBadges:
    report
  • Denny Cherry
    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.
    66,065 pointsBadges:
    report
  • Denny Cherry
    [...] syntax in SQL Server 2008 - 5.9k views 7. SQL Server 2012 Licensing Changes - 7.6k views 6. Difference between an Index and a Primary Key - 8.7k views 5. What exactly is MSDTC, any when do I need it? - 11.5k [...]
    0 pointsBadges:
    report
  • sangita90
    Very clear picture and easy to understand. Fully Impressed. Thank u....

    20 pointsBadges:
    report
  • sangita90
    hello Denny, if in one column there are 3 same values present, then the index value is also same?? please reply soon.
    20 pointsBadges:
    report
  • Denny Cherry

    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

    66,065 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to: