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:

SELECT INTO Filegroup

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

SELECT INTO Filegroup

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

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

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

SELECT INTO Filegroup

5. Run the SELECT INTO statement with SECONDARY filegroup:

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

SELECT INTO Filegroup

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