SQL Server 2017 – SELECT INTO [A SPECIFIC FILEGROUP]

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

newFG

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'

datacap

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'

newtable

 

 

One thought on “SQL Server 2017 – SELECT INTO [A SPECIFIC FILEGROUP]

  1. Pingback: Dew Drop - June 19, 2018 (#2749) - Morning Dew

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s