Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SELECT...INTO Enhancements in SQL Server 2017


By:   |   Last Updated: 2017-09-28   |   Comments (4)   |   Related Tips: More > SQL Server 2017

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


Last Updated: 2017-09-28


next webcast button


next tip button



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

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Wednesday, May 02, 2018 - 7:51:57 AM - Greg Robidoux Back To Top

Hi Amro,

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

-Greg


Wednesday, May 02, 2018 - 5:21:16 AM - Amro Selim Back To Top
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 03, 2017 - 8:18:30 AM - rajendra gupta Back To Top

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 03, 2017 - 8:01:29 AM - Munish Back To Top

 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

 


Learn more about SQL Server tools