Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

1 Answer

up vote 6 down vote accepted

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.

share|improve this answer
    
Thanks Aaron. Thats what I thought but when unsure... Ask the experts. –  Lumpy Mar 4 '13 at 15:36
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.