Question

Point out the wrong statement.

(a) Heap inserts are typically slower than using a clustered index

(b) Use partitions and partition SWITCH command; i.e., load a work table that contains a single partition and SWITCH it into the main table after you build the indexes and put the constraints on

(c) Use the NOLOCK or TABLOCK hints to remove locking overhead

(d) Use a commit size of <5000 to avoid lock escalation when inserting; note that in SQL Server 2008 you can now enable/disable lock escalation at the object level, but use this wisely

I had been asked this question during an online interview.

I want to ask this question from Integration Services topic in portion Data Connectivity of SQL Server

Answer 1

Right answer is (a) Heap inserts are typically slower than using a clustered index

For explanation I would say: For using Heap inserts, you may want to drop indexes and rebuild if you are changing a large part of the destination table; you will want to test your inserts both by keeping indexes in place and by dropping all indexes and rebuilding to validate.