Forget about Adaptive Query Processing for a minute, what other feature have I been waiting for? SELECT INTO a specific filegroup, not the default filegroup! I have needed this feature many times in the past. Let’s take a look at it using the WideWorldImportersDW database.

SELECT INTO [A SPECIFIC FILEGROUP]

I alter the database and create a new filegroup called cold data. Ultimately I will be moving data from a specific table into it.

USE [Master]
GO
ALTER DATABASE [WideWorldImportersDW] ADD FILEGROUP [ColdData]
GO
ALTER DATABASE [WideWorldImportersDW]
ADD FILE
(
 NAME = N'AdventureWorks2016CTP3_Demo',
 FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.SONIC\MSSQL\DATA\WideWorldImportersDWCold.ndf' ,
  SIZE = 5MB,
    MAXSIZE = 100MB,
    FILEGROWTH = 5MB
)
TO FILEGROUP [ColdData]
GO

I confirm that I successfully created the new filegroup.

USE [WideWorldImportersDW]
GO
SELECT * FROM sys.filegroups

This is where the magic happens. I use the basic SELECT INTO but state the filegroup called cold data and get data from the primary filegroup. Please note that the data still exists in the original table.

 SELECT *
INTO [WideWorldImportersDW].[Fact].[ColdOrders] ON [ColdData]
FROM  [WideWorldImportersDW].[Fact].[Order]
WHERE [Order Date Key] = '2013-01-01'

Data is still in the original table.

-- Still exists in the orginal table.
SELECT * FROM [Fact].[Order] WHERE [Order Date Key] = '2013-01-01'

Do not forget that data is also in the newly created table (on the new filegroup)…obviously.

Below, I just confirm that the new table is in the new filegroup.

EXEC sp_help 'Fact.ColdOrders'