sql primary key and index

Question

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?

Solution 1

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.

Solution 2

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.

Solution 3

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.

Solution 4

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

Solution 5

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.

View more solutions

Comments

[SQLMenace]

That is because the PK is a clustered index, look at your query plan

[Pacerier]

The damage of unused indexes are very harmful indeed. For one thing, indexes eat up storage. For another thing, it slows down writes and updates. Always delete indexes that are not going to be used.

[quillbreaker]

What if the key is a int or bigint autoincrement key? Is SQL Server smart enough to not do a unique index scan in this case?

[Admin]

@quillbreaker: an IDENTITY field is not guaranteed to be unique. After all, users can insert duplicate values if they user IDENTITY_INSERT.

[Charles Burns]

I know this is an ancient topic, but I don't understand how a uniqueness scan of one index would be such a load on the system. A B+tree scan should be O(log n) * v where v is constrained overhead for index fragmentation, imperfect tree balance, etc.. Thus 2 billion rows would be log base 2 of 2,000,000,000 (about 31 seeks) times, say, 2 or 3 or even 10. 40M inserts per day is about 462/sec, ~100 IO's per insert... Ahh... Oh. I see. And this was before widespread SSDs.

[Max Candocia]

Unless you dropped the uniqueness constraint, wouldn't the overhead of checking each of the rows for uniqueness be much greater?

[Susobhan Das]

this is the answer I Was looking for - "why I should create index?"