Sneak peek on SQL 2014: Parallel SELECT ... INTO!
We are all exited about SQL 2014. I'm sure you have already downloaded the CTP. In this short post I would like to show you a welcome feature that you might not know about :).
Suppose to have a large, parallel query. Until now if you wanted to create a table out of that query you would end up using a non-parallel operator. That means SQL Server would have to gather all the parallel streams before inserting into your table.
With the upcoming new release of SQL Server this will be no longer true! SQL Server 2014 improved the SELECT ... INTO statement so it can now operate in parallel. Want to try yourself?
Create a sample database like this:
CREATE DATABASE Parallel;
GO
USE Parallel;
GO
CREATE TABLE tb1(ID INT IDENTITY(1,1), Testo VARCHAR(100) DEFAULT(GETDATE()));
GO
CREATE TABLE tb2(ID INT IDENTITY(1,1), IDtb1 INT);
GO
INSERT INTO tb1 DEFAULT VALUES;
GO 100
INSERT INTO tb2(IDtb1)
SELECT ID FROM tb1;
GO 100
INSERT INTO tb1(Testo)
SELECT Testo FROM tb1
GO 13
And then execute this query:
SELECT T.*
INTO #tbl
FROM tb1 T
INNER JOIN tb2 B ON T.ID = B.IDtb1
WHERE T.Testo
BETWEEN
(SELECT MAX(Testo) FROM tb1) AND
(SELECT MIN(Testo) FROM tb1);
Up to SQL 2012 the query plan should be something like this:
In SQL 2014 CTP1 this is what you should get:
Notice the improved operator and how it affects the query plan:
You can find confirmation of this here: [MSDN] What's New (Database Engine).
Happy Coding,