Arjun Sivadasan (8/13/2010)
1. Do I create PK before or after the insert (or if it depends, what are the factors)?
well it depends , sorting willbe done in either of cases, but in case of inserting data into heap then creating index give benefit.i think two operation ( insertion and index creation ) hapoen one by one ,results in less locking.but again , testing would give concrete answer for your query.
Arjun Sivadasan (8/13/2010)
2. The two queries attached have different execution times. From the execution plans, in case of primary key creation after insert, after insertion of data the following steps happen - Table scan-->Sort-->Index Insert. I had thought that data will only be reorganized in this case. What happens during the steps 'in bold' and the 'Index Insert' step in particular?
index page creation
Arjun Sivadasan (8/13/2010)
3. If I use table instead of temporary table, is the behavior similar?
it should.
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)