Summary: I have a table populated via the following:

insert into the_table (...) select ... from some_other_table

Running the above query with no primary key on the_table is ~15x faster than running it with a primary key, and I don't understand why.

The details: I think this is best explained through code examples.

I have a table:

create table the_table (
    a int not null,
    b smallint not null,
    c tinyint not null
);

If I add a primary key, this insert query is terribly slow:

alter table the_table
    add constraint PK_the_table primary key(a, b);

-- Inserting ~880,000 rows
insert into the_table (a,b,c)
    select a,b,c from some_view;

Without the primary key, the same insert query is about 15x faster. However, after populating the_table without a primary key, I can add the primary key constraint and that only takes a few seconds. This one really makes no sense to me.

More info:

• The estimated execution plan shows 0% total query time spent on the clustered index insert
• SQL Server 2008 R2 Developer edition, 10.50.1600

Any ideas?