Sql primary key and index

Question

Let's say I have an ID (int) string in the database specified as the primary key. If I often request an identifier, do I also need to index it? Or does this primary key mean that it is already indexed?

The reason I am asking for is because in MS SQL Server I can create an index for this identifier, which, as I said, is my main key.

Edit: An additional question is - will it hurt to additionally index the primary key?

Answer 1

You are right, this confuses that SQL Server allows you to create duplicate indexes in the same field. But the fact that you can create another does not indicate that the PK index also does not exist.

An extra index is not suitable, but the only harm (very small) is the extra file size and the overhead of creating lines.

Answer 2

As everyone else has said, primary keys are automatically indexed.

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

For example, you have a table with many columns, but you only query the columns ID, Name and Address. Taking the identifier as the primary key, we can create the following index, which is built on the ID, but includes columns of names and addresses.

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 only give you the result using the index you created and will not read anything from the actual table.

Answer 3

NOTE. This answer relates to enterprise-class development.

This is an RDBMS problem, not just SQL Server, and the behavior can be very interesting. For one, although it is usually indexed automatically (unambiguously) for primary keys, it is NOT absolute. There are times when it is important that the primary key is NOT uniquely indexed.

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

Now for the fun part - when do you NOT want a unique primary key index? You do not want this and cannot endure one when your table receives enough data (rows) to maintain the index index too expensive. It depends on the hardware, the RDBMS mechanism, the characteristics of the table and database, and the system load. However, it usually begins to appear as soon as the table reaches several million rows.

A significant problem is that each row insertion or update of a primary key column scans the index for uniqueness. This unique index scan (or its equivalent in any of the DBMSs) becomes much more expensive as the table grows until it becomes dominant in the performance of the table.

I have repeatedly dealt with this problem with tables up to two billion rows long, 8 TB of storage and forty million row inserts per day. I was instructed to redesign the system involved, which included abandoning the unique primary key index practically as a first step. Indeed, the abandonment of this index was necessary in production simply to recover from a failure before we even approached the redesign. This redesign included finding other ways to ensure the uniqueness of the primary key and provide quick access to data.

Answer 4

Primary keys are always indexed by default.

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

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

Answer 5

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

Answer 6

Here's the transition from MSDN :

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

Answer 7

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

Answer 8

Well in SQL Server, as a rule, the primary key is automatically indexed. This is true, but it does not guarantee a faster request. The primary key will give you excellent performance if there is only 1 field as the primary key. But, when several fields are used as the primary key, the index is based on these fields.

For example: Field A, B, C is the primary key, so when you perform a query based on these three fields in your WHERE CLAUSE, the performance is good, BUT, when you want to query only the field C in WHERE CLAUSE, you will not get good performance. Thus, in order to improve performance and productivity, you will need to index the C field manually.

In most cases, you will not see the problem until you get more than 1 million records.

Answer 9

primary keys are automatically indexed

you can create additional indexes with pk depending on your usage

• index zip_code, an identifier can be useful if you often choose zip_code and id

Answer 10

I have a huge database without a separate index.

Each time I request a primary key, the results for all intensive purposes are instant.