Difference between an Index and a Primary Key

Published On: 2009-08-31By:

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


Contact the Author | Contact DCAC

6 responses to “Difference between an Index and a Primary Key”

  1. RaghuCD says:

    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?

  2. Mrdenny says:

    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.

  3. […] 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 […]

  4. sangita90 says:

    Very clear picture and easy to understand. Fully Impressed. Thank u….

  5. sangita90 says:

    hello Denny, if in one column there are 3 same values present, then the index value is also same?? please reply soon.

  6. Denny Cherry says:

    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

Video

Globally Recognized Expertise

As Microsoft MVP’s and Partners as well as VMware experts, we are summoned by companies all over the world to fine-tune and problem-solve the most difficult architecture, infrastructure and network challenges.

And sometimes we’re asked to share what we did, at events like Microsoft’s PASS Summit 2015.

Awards & Certifications

Microsoft Partner    Microsoft MVP    Microsoft Certified Master    VMWare Partner    VMWare vExpert
   Best Full-Service Cloud Technology Consulting Company    Insights Sccess Award    Technology Headlines Award    Golden Bridge Gold Award    CIO Review Top 20 Azure Solutions Providers
Share via