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:
- 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.
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:
Prince Kumar Rastogi