After getting 100,000 rows of Data using the 4 main flavours of Insert Statement I attempted to determine which method was the fastest.. To ensure that the results were not skewed by database re-sizing actions or cached data. I ran each query multiple times and used the following command to clear the CACHE
CHECKPOINT
DBCC FREEPROCCACHE -- to clear the procedure cache
DBCC
DROPCLEANBUFFERS -- to test queries with a cold buffer cache
I said 'attempted' because I found out that with Method 3 Insert with Select Values Clause and using 100,000 rows on my laptop I could not get the query to complete - It just 'hung' (I gave up after 40 minutes) I assume because it needed to materialise the 100,000 rows in memory before it could attempt to Insert them..
I decided that to make it a test whereby at least I got a result - I therefore limited the number of rows to 1000, this also mean't that it was within the limits of the Insert Statement with Row Constructor method. I put in a Statistics statements and executed the code a number of times.
The average results (10 runs) were as follows:
Method 1 Classic Insert:
-- 274 ms
Method 2 Insert with row constructor:
-- 1413 ms
Method 3 Insert with Select Values Clause:
-- 1540 ms
Method 4 Merge Statement:
-- 1574 ms
As can be seen the fastest method (reduction of at least 80%) is the original Classic Insert Statement this was really surprisng to me, I wondered why - One obvious change is the locking involved. The Classic Insert Statement was taking Exclusive (X) on Index Key Locks and Intent Exclusive (IX) Locks on Pages and Tables while the other types seem to be taking Schema Stability (Sch-S) Locks on Table and Metadata and Shared (S) Database Locks. Probably because they ALL have to materialise the Insert data in some form before they marshall it to the Insert statement...
Conclusions
1) Many ways to Insert Data in SQL Server
2) Easiest to write probably Insert with Row Constructor - Though this is limited to 1000 rows per batch
3) Best for maintainability and control is the MERGE statement
4) Fastest method is Classic Insert - and Insert with Select Values Clause will probably never finish with a very large number of rows
See also
http://consultingblogs.emc.com/paulmcmillan/archive/2011/07/06/how-many-different-ways-can-you-write-a-sql-insert-statement-1-2-3-6.aspx
http://consultingblogs.emc.com/paulmcmillan/archive/2011/07/06/so-now-you-can-write-an-insert-statement-6-different-ways-why-do-you-need-to-know-this-and-why-is-merge-the-best.aspx
http://consultingblogs.emc.com/paulmcmillan/archive/2011/07/06/a-gotcha-with-the-insert-statement-row-constructor.aspx