Reputation: 1665

sql primary key and index

Say I have an ID row (int) in a database set as the primary key. If I query off the ID often do I also need to index it? Or does it being a primary key mean it's already indexed?

Reason I ask is because in MS SQL Server I can create an index on this ID, which as I stated is my primary key.

Edit: an additional question - will it do any harm to additionally index the primary key?

Upvotes: 118

Answers (11)

gotqn

Reputation: 43666

Declaring a PRIMARY KEY or UNIQUE constraint causes SQL Server to automatically create an index.

An unique index can be created without matching a constraint, but a constraint (either primary key or unique) cannot exist without having a unique index.

From here, the creation of a constraint will:

cause an index with the same name to be created
deny dropping the created index as constraint is not allowed to exists without it

and at the same time dropping the constraint will drop the associated index.

So, is there actual difference between a PRIMARY KEY or UNIQUE INDEX:

NULL values are not allowed in PRIMARY KEY, but allowed in UNIQUE index; and like in set operators (UNION, EXCEPT, INTERSECT), here NULL = NULL which means that you can have only one value as two NULLs are find as duplicates of each other;
only one PRIMARY KEY may exists per table while 999 unique indexes can be created
when PRIMARY KEY constraint is created, it is created as clustered unless there is already a clustered index on the table or NONCLUSTERED is used in its definition; when UNIQUE index is created, it is created as NONCLUSTERED unless it is not specific to be CLUSTERED and such already does not exist;

Upvotes: 7

MicSim

Reputation: 26816

Here the passage from the MSDN:

When you specify a PRIMARY KEY constraint for a table, the Database Engine enforces data uniqueness by creating a unique index for the primary key columns. This index also permits fast access to data when the primary key is used in queries. Therefore, the primary keys that are chosen must follow the rules for creating unique indexes.

Upvotes: 9

jcollum

Reputation: 46613

Primary keys are always indexed by default.

You can define a primary key in SQL Server 2012 by using SQL Server Management Studio or Transact-SQL. Creating a primary key automatically creates a corresponding unique, clustered or nonclustered index.

http://technet.microsoft.com/en-us/library/ms189039.aspx

Upvotes: 23

Susanto Siman

Reputation: 11

Well in SQL Server, generally, primary key is automatically indexed. This is true, but it not guaranteed of faster query. The primary key will give you excellent performance when there is only 1 field as primary key. But, when there are multiple field as primary key, then the index is based on those fields.

For example: Field A, B, C are the primary key, thus when you do query based on those 3 fields in your WHERE CLAUSE, the performance is good, BUT when you want to query with Only C field in the WHERE CLAUSE, you wont get good performance. Thus, to get your performance up and running, you will need to index C field manually.

Most of the time, you wont see the issue till you hits more than 1 million records.

Upvotes: 1

Rob Williams

Reputation: 7921

NOTE: This answer addresses enterprise-class development in-the-large.

This is an RDBMS issue, not just SQL Server, and the behavior can be very interesting. For one, while it is common for primary keys to be automatically (uniquely) indexed, it is NOT absolute. There are times when it is essential that a primary key NOT be uniquely indexed.

In most RDBMSs, a unique index will automatically be created on a primary key if one does not already exist. Therefore, you can create your own index on the primary key column before declaring it as a primary key, then that index will be used (if acceptable) by the database engine when you apply the primary key declaration. Often, you can create the primary key and allow its default unique index to be created, then create your own alternate index on that column, then drop the default index.

Now for the fun part--when do you NOT want a unique primary key index? You don't want one, and can't tolerate one, when your table acquires enough data (rows) to make the maintenance of the index too expensive. This varies based on the hardware, the RDBMS engine, characteristics of the table and the database, and the system load. However, it typically begins to manifest once a table reaches a few million rows.

The essential issue is that each insert of a row or update of the primary key column results in an index scan to ensure uniqueness. That unique index scan (or its equivalent in whichever RDBMS) becomes much more expensive as the table grows, until it dominates the performance of the table.

I have dealt with this issue many times with tables as large as two billion rows, 8 TBs of storage, and forty million row inserts per day. I was tasked to redesign the system involved, which included dropping the unique primary key index practically as step one. Indeed, dropping that index was necessary in production simply to recover from an outage, before we even got close to a redesign. That redesign included finding other ways to ensure the uniqueness of the primary key and to provide quick access to the data.

Upvotes: 29

red.clover

Reputation: 1828

As everyone else have already said, primary keys are automatically indexed.

Creating more indexes on the primary key column only makes sense when you need to optimize a query that uses the primary key and some other specific columns. By creating another index on the primary key column and including some other columns with it, you may reach the desired optimization for a query.

For example you have a table with many columns but you are only querying ID, Name and Address columns. Taking ID as the primary key, we can create the following index that is built on ID but includes Name and Address columns.

CREATE NONCLUSTERED INDEX MyIndex
ON MyTable(ID)
INCLUDE (Name, Address)

So, when you use this query:

SELECT ID, Name, Address FROM MyTable WHERE ID > 1000

SQL Server will give you the result only using the index you've created and it'll not read anything from the actual table.

Upvotes: 61

dkretz

Reputation: 37655

You are right, it's confusing that SQL Server allows you to create duplicate indexes on the same field(s). But the fact that you can create another doesn't indicate that the PK index doesn't also already exist.

The additional index does no good, but the only harm (very small) is the additional file size and row-creation overhead.

Upvotes: 81