SELECT...INTO Enhancements in SQL Server 2017

By: Rajendra Gupta | Comments (4) | Related: > TSQL

Problem

With SQL Server 2017 there are many new exciting feature and improvements over previous versions. In this tip we will cover the new feature SELECT...INTO that puts data into a specific filegroup.

Solution

Filegroups are named collections of files and are used to help with data placement and administrative tasks such as backup and restore operations. Each database has a primary filegroup. This filegroup contains the primary data file and any secondary files that are not put into other filegroups. User-defined filegroups can be created to group data files together.

So basically filegroups are:

Filegroups are logical database structure that organize physical data files together.
Objects are created and stored in filegroups.
SQL Server has an initial filegroup (PRIMARY) which we cannot change the name of the this filegroup. The PRIMARY filegroup must hold the .MDF file, which contains metadata about the database. It cannot be removed either.
Only one filegroup can have the "Default" property, which means if we don't specify the filegroup when creating tables, indexes, etc. (with the clause ON [Filegroup_name] at the end of the CREATE statement), the object will be created in the default filegroup.

All data files are stored in filegroups which are listed in sys.filegroups.

Primary: The filegroup that contains the primary file. All system tables are allocated to the primary filegroup.
User-defined: Any filegroup that is specifically created by the user when the user first creates or later modifies the database.

SELECT INTO statement

The SELECT INTO statement creates a new table and populates it with the result set of the SELECT statement. SELECT INTO can be used to combine data from several tables or views into one table. It can also be used to create a new table that contains data selected from a linked server. The structure of the new table is defined by the attributes of the expressions in the select list.

As you might have used SELECT INTO to create a new table and copy the data into the new data. Until SQL Server 2016, if we use SELECT INTO, the table will be created in the default filegroup. We do not have the ability to create the new table into a filegroup other than the default filegroup. SQL Server 2017 provides the ability to specify the filegroup for newly created table within SELECT INTO statement.

Create Test Database

Let's see how this feature works. I will be using the AdventureWorks2016CTP3 database to perform the demo.

First we will check the existing database filegroup in the AdventureWorks2016CTP3 database.

sp_helpdb 'AdventureWorks2016CTP3'
go

We can see the database consists of a Primary filegroup only. Let's create a secondary filegroup and add a secondary data file into this.

USE [Master]
GO

ALTER DATABASE [AdventureWorks2016CTP3] ADD FILEGROUP [SECONDARY]
GO

ALTER DATABASE [AdventureWorks2016CTP3]
ADD FILE 
( 
 NAME = N'AdventureWorks2016CTP3_Demo', 
 FILENAME = N'C:\mssqltips\AdventureWorks2016CTP3_Demo.ndf' , 
  SIZE = 5MB,  
    MAXSIZE = 100MB,  
    FILEGROWTH = 5MB
) 
TO FILEGROUP [SECONDARY]
GO

Now verify the database properties for the secondary filegroup.

SQL Server Database Properties Secondary Filegroup

If we look at the database properties for filegroups, we can also see that the default filegroup is set to the PRIMARY.

Using SELECT INTO for SQL Server 2016 and earlier

Syntax for SELECT...INTO for SQL Server 2016 and earlier is shown below:

SELECT column1, column2, column3, ...
INTO newtable 
FROM oldtable
WHERE condition;

Now if we use SELECT...INTO the new table will be created in the default filegroup, which is the PRIMARY.

 
SELECT * 
INTO [AdventureWorks2016CTP3].[Production].[Product_Demo]
FROM [AdventureWorks2016CTP3].[Production].[Product]

We can see below that the new table is created in the default filegroup PRIMARY.

Using SELECT INTO for SQL Server 2017

In SQL Server 2017 we can specify the filegroup for the SELECT INTO statement.

This is the new syntax with the new ON FILEGROUP option.

 
SELECT column1, column2, column3, ...
INTO newtable ON FILEGROUP
FROM oldtable 
WHERE condition;

Here is the command.

 
SELECT * 
INTO [AdventureWorks2016CTP3].[Production].[Product_Demo_Filegroup] ON [SECONDARY]
FROM [AdventureWorks2016CTP3].[Production].[Product]

We can see this was put in the SECODNARY filegroup if we view the new table properties.

We can also verify using SSMS by right clicking on the table name and selecting Properties > Storage.

SQL Server Table Properties FileGroup using SSMS

If we script out the table using the Script table feature, we can see that it also specifies the right filegroup for the table.

Here is the generated script.

USE [AdventureWorks2016CTP3]
GO

/****** Object:  Table [Production].[Product_Demo_Filegroup] Script Date: 26/08/2017 21:32:23 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [Production].[Product_Demo_Filegroup](
 [ProductID] [int] IDENTITY(1,1) NOT NULL,
 [Name] [dbo].[Name] NOT NULL,
 [ProductNumber] [nvarchar](25) NOT NULL,
 [MakeFlag] [dbo].[Flag] NOT NULL,
 [FinishedGoodsFlag] [dbo].[Flag] NOT NULL,
 [Color] [nvarchar](15) NULL,
 [SafetyStockLevel] [smallint] NOT NULL,
 [ReorderPoint] [smallint] NOT NULL,
 [StandardCost] [money] NOT NULL,
 [ListPrice] [money] NOT NULL,
 [Size] [nvarchar](5) NULL,
 [SizeUnitMeasureCode] [nchar](3) NULL,
 [WeightUnitMeasureCode] [nchar](3) NULL,
 [Weight] [decimal](8, 2) NULL,
 [DaysToManufacture] [int] NOT NULL,
 [ProductLine] [nchar](2) NULL,
 [Class] [nchar](2) NULL,
 [Style] [nchar](2) NULL,
 [ProductSubcategoryID] [int] NULL,
 [ProductModelID] [int] NULL,
 [SellStartDate] [datetime] NOT NULL,
 [SellEndDate] [datetime] NULL,
 [DiscontinuedDate] [datetime] NULL,
 [rowguid] [uniqueidentifier] NOT NULL,
 [ModifiedDate] [datetime] NOT NULL
) ON [SECONDARY]
GO

Summary

From the above demo we can see that a table can be created on the fly in a filegroup other than the default filegroup. The files behind that filegroup could be on a separate drive allowing to separate the IO of these processes away from the day-to-day database operations.

Note: Currently SELECT INTO in SQL Server 2017 doesn't support memory optimized filegroups.

This is a very useful feature enhancement which has been introduced in SQL Server 2017 which helps us to organize table creation into different filegroups at the time of creation.

Next Steps

Read more about SELECT..INTO.
Get overview of SQL Server backups.
Explore Database Files and Filegroups.
Read more about SQL Server 2017 Tips.

About the author

Rajendra Gupta is a Consultant DBA with 14+ years of extensive experience in database administration including large critical OLAP, OLTP, Reporting and SharePoint databases.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips