SQL Server 2017: SELECT INTO File Group

With the CTP2 build of SQL Server 2017, you now have the ability of doing a SELECT INTO operation into a specific filegroup.


The syntax looks like this

SELECT * INTO TableName ON FileGroup FROM SomeQuery

 What is the use of this you might ask? Well maybe you have some ad-hoc queries where you save some data but you don't want it sitting on your expensive SAN. Or maybe you populate staging tables on the fly and you want it to end up on a specific SSD because you need the speed of the SSD disk for these operations.

Of course all this can be done by creating the table first but this saves you at least some time..especially when all you have is a query and the data types are maybe unknown



Let's see how this all works, first create a database with just 1 data file and 1 log file

CREATE DATABASE [TestSelectInto]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'TestSelectInto', 
 FILENAME = N'C:\DATA\TestSelectInto.mdf' , 
 SIZE = 6MB , FILEGROWTH = 6MB )
 LOG ON 
( NAME = N'TestSelectInto_log', 
 FILENAME = N'C:\DATA\TestSelectInto_log.ldf' , 
 SIZE = 6MB , FILEGROWTH = 6MB )
GO

Now, add a new filegroup and name it ScratchFG

ALTER DATABASE [TestSelectInto] ADD FILEGROUP ScratchFG
GO

ALTER DATABASE [TestSelectInto]
ADD FILE
(   NAME= 'ScratchData',
    FILENAME = N'C:\DATA\ScratchData.mdf', SIZE = 6MB , FILEGROWTH = 6MB
) TO FILEGROUP ScratchFG;

Now that we added the filegroup, let's verify that we have 3 filegroups

USE TestSelectInto
GO

SELECT file_id,type_desc,name,physical_name 
FROM sys.database_files

file_id type_desc name physical_name
1 ROWS TestSelectInto C:\DATA\TestSelectInto.mdf
2 LOG TestSelectInto_log C:\DATA\TestSelectInto_log.ldf
3 ROWS ScratchData C:\DATA\ScratchData.mdf

As you can see, we have 3 filegroups

Now it is time to do our select into filegroup command, we will also do a select into without the filegroup specified

SELECT * INTO TestScratchFG ON ScratchFG 
FROM master..spt_values


SELECT * INTO TestDefault  
FROM master..spt_values

How can we be sure where the table ended up? You can use the query below

SELECT OBJECT_SCHEMA_NAME(t.object_id) AS schema_name,
t.name AS table_name,
i.index_id,
i.name AS index_name,
fg.name AS filegroup_name
FROM sys.tables t
INNER JOIN sys.indexes i ON t.object_id = i.object_id
INNER JOIN sys.filegroups fg ON i.data_space_id=fg.data_space_id

Here is what we see, the TestScratchFG table got created on the ScratchFG filegroup, the TestDefault table got created on the primary filegroup

schema_name table_name index_id index_name filegroup_name
dbo TestScratchFG 0 NULL ScratchFG
dbo TestDefault 0 NULL PRIMARY




What about temporary tables, can you use this syntax? Let's try.....

SELECT * INTO #TestScratchFG ON ScratchFG 
FROM master..spt_values

Msg 1921, Level 16, State 1, Line 60
Invalid filegroup 'ScratchFG' specified.

So as you can see, you cannot specify the filegroup of the DB that you are in...what you can do however is specifying primary

SELECT * INTO #TestScratchFG ON [primary]
FROM master..spt_values

Since you cannot add filegroups to tempdb, primary is all you can specify...but then again why would you..there is no point. So if you are using temporary table, this syntax is useless.

That is all for this post, hopefully, you have seen how easy it is to use this and it might be of use to you in the future