SELECT INTO is faster than INSERT SELECT

I had heard that SELECT INTO is faster than INSERT SELECT, so I wanted to prove it.

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

SQL Server CDC: Remember It's Not Free

Someone recently recommended that a developer use SQL Server's CDC (Change Data Capture) to create a history record for a transactional table because doing that work in a stored procedure "makes this stored procedure slow". Remember that there is no magic in CDC; it's not free.

Based on the research I did for this article:

Performance Testing SQL Server 2008's Change Data Capture functionality

"The average penalty for CDC with full recovery is 10.51%."

SQLskills' "IE1: Immersion Event on Internals and Performance"

I just attended the most intense and valuable week of training I have ever experienced: SQLskills' "IE1: Immersion Event on Internals and Performance". This is a full week of training given by Kimberly Tripp and Paul S. Randal.

The whole week was great. These are two of the best trainers in the business, period. In this class, they teach everything, so you're getting great content every minute of every day.

I highly recommend the class.

Declare DataType When Creating Computed Column in SQL Server

When creating a computed column in SQL Server, be sure to use CONVERT (or CAST) to specify the datatype of the computed column, otherwise SQL Server will make the choice for you. It may not make the choice that you expected.

Here is a very simple example that shows a computed column with two possible values: 0 and 1. With no covert, SQL Server defaults to int. By using convert, we can use bit. This simple example shows that using CONVERT drops the storage requirement from 4 bytes to a single bit.

Code

SET NOCOUNT ON;USE tempdb;GOIF EXISTS (SELECT * FROM sys.tables WHERE [object_id] = OBJECT_ID('dbo.TestTable'))
   DROP TABLE dbo.TestTable;GOCREATE TABLE dbo.TestTable (
   TestId      INT         IDENTITY,
   TestName    VARCHAR(25) NOT NULL,
   LamboNoConvert AS (
       CASE
       WHEN TestName LIKE 'Lamborghini%'
       THEN 1
       ELSE 0
       END
   ),
   LamboConvert AS (
       CASE
       WHEN TestName LIKE 'Lamborghini%'
       THEN CONVERT(bit, 1)
       ELSE CONVERT(bit, 0)
       END
   )
);GO
INSERT INTO dbo.TestTable (TestName)VALUES
   ('Lamborghini Aventador'),
   ('McLaren F1');
SELECT * FROM dbo.TestTable;
SELECT
   name                      AS ColumnName,
   TYPE_NAME(system_type_id) AS TypeNameFROM sys.columnsWHERE [object_id] = OBJECT_ID('dbo.TestTable');


Results

TestId      TestName                  LamboNoConvert LamboConvert
----------- ------------------------- -------------- ------------
1           Lamborghini Aventador     1              1
2           McLaren F1                0              0

ColumnName      TypeName
--------------- --------
TestId          INT
TestName        VARCHAR
LamboNoConvert  INT
LamboConvert    BIT

Computed Column References:

• Exploring Indexed Persisted Computed Columns
• More on Indexed Persisted Computed Columns