Here are the results of testing in both FULL and SIMPLE recover models. Each test was run twice, once with INSERT-SELECT first and once with SELECT-INTO first.
The net result is that, at least for this test code on my hardware, using SELECT INTO is an order of magnitude faster in SIMPLE recover mode than INSERT SELECT. Using SELECT INTO is faster in FULL recover mode than INSERT SELECT, but the difference is less significant.
Test Results
Recovery Model: Simple
Order: INSERT-SELECT first
Elapsed Mcs - INSERT-SELECT: 8,391,839
Elapsed Mcs - SELECT-INTO: 962,096
Order: SELECT-INTO first
Elapsed Mcs - INSERT-SELECT:7,205,720
Elapsed Mcs - SELECT-INTO: 918,092
Recovery Model: Full
Order: INSERT-SELECT first
Elapsed Mcs - INSERT-SELECT:7,095,709
Elapsed Mcs - SELECT-INTO:1,289,129
Order: SELECT-INTO first
Elapsed Mcs - INSERT-SELECT: 6,274,628
Elapsed Mcs - SELECT-INTO:1,902,190
Test Script
USE MASTER;
GO
IF EXISTS (SELECT * FROM sys.databases WHERE name = 'SelectInto') ALTER DATABASE SelectInto SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
IF EXISTS (SELECT * FROM sys.databases WHERE name = 'SelectInto') DROP DATABASE SelectInto;
GO
CREATE DATABASE SelectInto;
GO
ALTER DATABASE SelectInto SET RECOVERY FULL;
--ALTER DATABASE SelectInto SET RECOVERY SIMPLE;
GO
USE SelectInto;
GO
-- Source table: t1
CREATE TABLE dbo.t1 (
ColID INT NOT NULL,
Col1 CHAR(30) NOT NULL DEFAULT 'asdf',
CONSTRAINT PK_t1 PRIMARY KEY CLUSTERED (ColID)
);
-- Populate t1
DECLARE @MaxRows INT = 1000000;
DECLARE @i INT = 0;
WHILE @i < @MaxRows
BEGIN
SET @i += 1;
INSERT INTO dbo.t1 (ColID) VALUES (@i);
END;
-- Destination heap: t2
CREATE TABLE dbo.t2 (
ColID INT NOT NULL,
Col1 CHAR(30) NOT NULL DEFAULT 'asdf'
);
DECLARE @t1 time(7);
DECLARE @t2 time(7);
-- INSERT/SELECT
SET @t1 = SYSUTCDATETIME();
INSERT INTO dbo.t2 (ColID, Col1)
SELECT ColID, Col1 FROM dbo.t1;
SET @t2 = SYSUTCDATETIME();
SELECT DATEDIFF(mcs, @t1, @t2) AS ElapsedMcs;
-- SELECT INTO
SET @t1 = SYSUTCDATETIME();
SELECT ColID, Col1
INTO dbo.t3
FROM dbo.t1;
SET @t2 = SYSUTCDATETIME();
SELECT DATEDIFF(mcs, @t1, @t2) AS ElapsedMcs;
References
- INTO Clause at MSDN
