Primary Key: Slow Inserts?

Question

Defining a column to be a primary in table on SQL Server - will this make inserts slower?

I ask because I understand this is the case for indexes.

The table has millions of records.

Answer 1

No, not necessarily! Sounds counter-intuitive, but read this quote from Kim Tripp's blog post:

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.

So actually, having a good clustered index (e.g. on a INT IDENTITY column, if ever possible) does speed things up - even insert, updates and deletes!

Answer 2

Primary keys are automatically indexed, clustered if possible and failing that non-clustered.

So in that sense inserts are slightly affected, but of course having no primary key would usually be much much worse, assuming the table needs a primary key.

First measure, identify a problem and then try to optimize. Optimizing away primary keys is a very bad idea in general.