sql primary key and index

SqlSql ServerTsqlIndexingPrimary Key

Sql Problem Overview


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?

Sql Solutions


Solution 1 - Sql

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 - Sql

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 - Sql

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 - Sql

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 - Sql

Here the passage from the [MSDN][1]:

> 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.

[1]: http://msdn.microsoft.com/en-us/library/ms191236.aspx "MSDN"

Solution 6 - Sql

a PK will become a clustered index unless you specify non clustered

Solution 7 - Sql

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;

Solution 8 - Sql

Making it a primary key should also automatically create an index for it.

Solution 9 - Sql

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.

Solution 10 - Sql

primary keys are automatically indexed

you can create additional indices using the pk depending on your usage

  • index zip_code, id may be helpful if you often select by zip_code and id

Solution 11 - Sql

I have a huge database with no (separate) index.

Any time I query by the primary key the results are, for all intensive purposes, instant.

Attributions

All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
QuestiondanifoView Question on Stackoverflow
Solution 1 - SqldkretzView Answer on Stackoverflow
Solution 2 - Sqlred.cloverView Answer on Stackoverflow
Solution 3 - SqlRob WilliamsView Answer on Stackoverflow
Solution 4 - SqljcollumView Answer on Stackoverflow
Solution 5 - SqlMicSimView Answer on Stackoverflow
Solution 6 - SqlSQLMenaceView Answer on Stackoverflow
Solution 7 - SqlgotqnView Answer on Stackoverflow
Solution 8 - SqlE.J. BrennanView Answer on Stackoverflow
Solution 9 - SqlSusanto SimanView Answer on Stackoverflow
Solution 10 - SqlmsonView Answer on Stackoverflow
Solution 11 - SqlGrantView Answer on Stackoverflow