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;
CREATE TABLE tb1(ID INT IDENTITY(1,1), Testo VARCHAR(100) DEFAULT(GETDATE()));
CREATE TABLE tb2(ID INT IDENTITY(1,1), IDtb1 INT);
INSERT INTO tb1 DEFAULT VALUES;
INSERT INTO tb2(IDtb1)
SELECT ID FROM tb1;
INSERT INTO tb1(Testo)
SELECT Testo FROM tb1
And then execute this query:
FROM tb1 T
INNER JOIN tb2 B ON T.ID = B.IDtb1
(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).
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...
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