次の方法で共有

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,

Francesco Cogno

Comments

  • Anonymous
    January 01, 2003
    Yes I agree with you: SSIS is still the best way of loading data into precreated tables. The SELECT...INTO parallel feature helps in a very specific scenarios, (ie as temporary storage for long SPs) but will be automatic and safe (provided you raise your compatibility level to 110).

    Cheers,
    Francesco
  • Anonymous
    April 11, 2014
    this is great... but does this work with insert into too?
    insert into table select ... from query....
    if no, then SSIS still the best way to go...