SQL Server 2017 – SELECT INTO on FileGroup

Microsoft has announced SQL Server 2017 CTP version with lots of new features and improvements as compare to previous version i.e. SQL Server 2016. You can download the SQL Server 2017 in the fastest way as explained by Avanish in his recent post. In this blog post we will learn about the new feature of SQL Server 2017 for SELECT INTO statement i.e. – SELECT INTO on FileGroup Option.

As a database professional, we all used select into statements in our day to day activities. There might be different reasons to use this:

• I want to copy all the data of a table to another new table.
• I want to copy some columns of a table to another new table.

Till SQL Server 2016, SELECT INTO creates a new table and copy the data but new table always created into the default Filegroup. In most of the cases or generally PRIMARY Filegroup configured as a default Filegroup because this is the default setting for the databases. Think about the situation where you wants to create the table (table created due to SELECT INTO statement) on some different Filegroup (which is not the default filegroup). In SQL Server 2017 CTP, Microsoft provided the option to specify the Filegroup for newly created table within SELECT INTO statement.

Let me show you the same using example:

1. Create a new database FGTest and check the created files:

USE [Master]
GO
CREATE DATABASE FGTest;
GO
sp_helpdb FGTest
GO

2. Create a new Filegroup and create a file in that Filegroup:

USE [Master]
GO
ALTER DATABASE [FGTest] ADD FILEGROUP [SECONDARY]
GO
ALTER DATABASE [FGTest] 
ADD FILE 
( 
	NAME = N'FGTest_S1', 
	FILENAME = N'D:\Program Files\Microsoft SQL Server\MSSQL14.VNEXTCTP20\MSSQL\DATA\FGTest_S1.ndf' , 
	SIZE = 65536KB , 
	FILEGROWTH = 65536KB 
) 
TO FILEGROUP [SECONDARY]
GO
sp_helpdb FGTest
GO

3. Create a new table in the database and insert some data:

USE [FGTest]
GO

CREATE TABLE tbl_SelectIntoTest
(
RecordID INT IDENTITY(1,1) NOT NULL,
FName VARCHAR(50),
LName VARCHAR(50),
City VARCHAR(100),
DeptID INT NOT NULL
) ON [PRIMARY]
GO

CREATE CLUSTERED INDEX CIX_tbl_SelectIntoTest_RecordID ON tbl_SelectIntoTest(RecordID)
GO

SET NOCOUNT ON;

INSERT INTO tbl_SelectIntoTest VALUES('Zakir','Ali','Gurgaon',3)
GO 500
INSERT INTO tbl_SelectIntoTest VALUES('Gurpreet','Singh','Gurgaon',7)
GO 500
INSERT INTO tbl_SelectIntoTest VALUES('Prince','Rastogi','Noida',4)
GO 500

4. Run the SELECT INTO statement without specified any filegroup:

SELECT FName, LName, City 
INTO TempBackup
FROM tbl_SelectIntoTest 
WHERE DeptID=4

By default, newly created table will be in default filegroup i.e. PRIMARY Filegroup here.

sp_help TempBackup

5. Run the SELECT INTO statement with SECONDARY filegroup:

SELECT FName, LName, City 
INTO TempBackup2 ON [SECONDARY]
FROM tbl_SelectIntoTest 
WHERE DeptID=4

Now, newly created table will be in specified filegroup i.e. SECONDARY Filegroup here:

sp_help TempBackup2

HAPPY LEARNING!

Regards:
Prince Kumar Rastogi

Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook
Follow Prince Rastogi on Twitter | Follow Prince Rastogi on FaceBook