SELECT...INTO Enhancements in SQL Server 2017

By:   |   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
SQL Server Database Properties

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.

SQL Server Database Properties Secondary Filegroup

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.

SQL Server Table Properties

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.

SQL Server Table Properties FileGroup

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.

SQL Server Table Properties FileGroup

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


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Rajendra Gupta 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



Comments For This Article




Wednesday, May 2, 2018 - 7:51:57 AM - Greg Robidoux Back To Top (75840)

Hi Amro,

thanks for pointing that out.  This has been correct in the tip.

-Greg


Wednesday, May 2, 2018 - 5:21:16 AM - Amro Selim Back To Top (75838)
FOR THIS CODE it does not work : 

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

it should be on below format :

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

Tuesday, October 3, 2017 - 8:18:30 AM - rajendra gupta Back To Top (66839)

Munish-it may be due to multiple factors. How big is the table and you moving all data from all columns or specific columns. 

 

Any constraint defined on the table?

 


Tuesday, October 3, 2017 - 8:01:29 AM - Munish Back To Top (66835)

 Hi,

I have a query which insert data from one table to another.

ex: insert into temp(id,......... upto 600 columns like id,name,etc) select id........... upto 600 columns like id,name,etc from temp_data;

This particular query is taking hours to execute. Kindly suggest or provide a workarround this particular query to optimize the performance.

 

Regards,

Munish Bhardwaj

 















get free sql tips
agree to terms