Will a primary key be added as a clustered index?

Question

I've inherited a database where no primary keys were defined on the tables. There are also no clustered indexes assigned to the tables.

If I perform an alter table to assign a primary key will this result in SQL Server also creating a clustered index? If so should I expect slowness on the database due to IO from the data being repositioned on the harddisk?

Answer 1

Yes, SQL Server will create the primary key as clustered by default, but you don't have to accept the defaults.

ALTER TABLE dbo.foo 
  ADD CONSTRAINT pk PRIMARY KEY (bar); -- clustered

ALTER TABLE dbo.foo 
  ADD CONSTRAINT pk PRIMARY KEY CLUSTERED (bar); -- clustered

ALTER TABLE dbo.foo 
  ADD CONSTRAINT pk PRIMARY KEY NONCLUSTERED (bar); -- non-clustered

And yes, you will see some I/O activity here, so if it is a busy system, best to save this for quieter hours or a maintenance period.