SELECT INTO with Parallelism

Lets see one more feature of SQL Server 2014

Its SELECT INTO with parallelism

Select into supports parallelism and due to this time for inserting data into table is reduced



But this feature works only when database compatibility level is 120

Lets see an example

CREATE TABLE t

(id INT)

CREATE CLUSTERED COLUMNSTORE INDEX idx ON t

DECLARE @i INT

SET @i = 1

WHILE @i <= 10000000

BEGIN

       INSERT INTO t

       VALUES (@i)

       SET @i = @i  + 1

END

Now we will compare perfromance with older compatibility level

ALTER DATABASE [AdventureWorks2012] SET COMPATIBILITY_LEVEL = 110

SELECT * INTO t1 FROM t

lets see plan for it

Also look at property of table insert ,it says parallel = false

Now we will run the query

It took 10 seconds


Now we will change compatibility level to 120

ALTER DATABASE [AdventureWorks2012] SET COMPATIBILITY_LEVEL = 120

SELECT * INTO t2 FROM t

lets see plan for it

Now look at property of table insert , it says parallel = true

Now we will run the query

It took 6 seconds

Its using parallelism while inserting data into table and hence speed is improved.

Here we can see the plan is different in both the cases.


Its a simply but noticable feature and do not requires any code change.