3

I have a table where I haven't explicitly defined a primary key, it's not really required for functionality... however a coworker suggested I add a column as a unique primary key to improve performance as the database grows...

Can anyone explain how this improves performance?

There is no indexing being used (I know I could add indexes to improve performance, what's not clear is how a primary key would also improve performance.)

The specifics

The main table is a log of user activity, it has a auto incrementing column for each entry, so it's already unique, but it isn't set as a primary key

This log table references activity tables which detail the specific activity, referenced by that autoincrementing entry in the main table. So the value is only unique in the main log table, there could be 100 entries in an activity table that reference that value as an identifier (ie. for session 212 Niall did these 500 things).

As you might guess the bulk of data is in the activity tables.

Improve this question
1
  • What does your table look like? What's the use case? Commented Feb 5, 2012 at 21:08

4 Answers 4

Reset to default
5

As Kimberly Tripp (the Queen of Indexing) clearly shows in her excellent blog post, The Clustered Index Debate Continues..., having a clustered index on your SQL Server table is beneficial - for all operations - yes, even for inserts!

To quote Kimberly:

Inserts are faster in a clustered table (but only in the "right" clustered table) than compared to a heap. The primary problem here is that lookups in the IAM/PFS to determine the insert location in a heap are slower than in a clustered table (where insert location is known, defined by the clustered key). Inserts are faster when inserted into a table where order is defined (CL) and where that order is ever-increasing.

Since your primary key will by default automatically create a clustered index on that column you define, I would argue that yes, having a primary (clustering) key on your SQL Server table - even a log table - does have positive performance effects.

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Fixed your link there... This is important, but I'd reinforce the point that the benefit described comes from having a clustered index, and not from the fact that the clustered index is the primary key. The fact that primary keys are by default clustered is an implementation detail, and an implementation detail of SSMS at that.
Another good read is Brad's Sure Guide to Indexes simple-talk.com/sql/database-administration/… The main take away is you should have a clustered index (PK or not) on your table. Depending on the expected number of rows, I would just create an smallint, int or bigint identity column.
This may require a seperate question, but if the column that is the clustered index is not actually used in any queries, is it still improving performance?
@NiallByrne: YES! Even just the fact that you have a clustered table vs. a heap already helps - a lot, in some cases.
2

Primary keys can help performance - it tells SQL Server something important about that field - that it's unique and NOT NULL. This can help create more efficient execution plans.

This MSDN reference on Improving SQL Server Performance is worth a read.

Quote:

When primary and foreign keys are defined as constraints in the database schema, the server can use that information to create optimal execution plans.

Improve this answer

Comments

0

A primary key automatically sets an index on the primary column. Setting an index to your table will increase performance on your queries.

You don't need to set a primary key to speed up your performance but you should set indexes to your table that will speed up your queries.

It depends on your queries and table what indexes make sense and which don't.

Improve this answer

Comments

0

To add to the above - generally if you frequently search on a field, it is a good candidate for an index. Also, searching on an integer ID is usually faster than a string, for example.

Indexes take more storage space, but can increase search performance on that field.

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.