Microsoft has improved the way Select * into works in SQL Server 2014 by enabling the Select * into to run in Parallel mode.In this mode the query will run parallel across available processors providing a significant improvement in performance. I have used the Production.TransactionHistory table from AdventureWorks database and altered the table to hold 27 million records for this illustration and named it Production.TransactionHistorySample.The database compatibility level should be set to a minimum of 110 for this feature to work.
The below code took only 4:53 minutes to execute in SQL Server 2014
The same code took 11:53 minutes to run in SQL Server 2008R2. So awesome is the parallel mode execution in SQL Server 2014. On a quicker note, a developer can make use of this feature in creating staging tables which will be very fast. Do try this feature when you get a chance and share with me your findings:-)