SQL 2017 Tips: Creating Table On The Fly Onto A Specific FileGroup

Introduction

One of the frequently used and the most popular features in SQLServer is the ability to create a table on the fly based on a query. This has saved my day on numerous occasions. Only thing I was missing in this was the ability to organize the table into its own filegroup of my choice

Why are filegroups necessary?

There are various advantages of organizing tables into separate filegroups, the main among them being  the below

  • For large tables it makes sense to move them to a separate filegroup
  • In case of tables where data size is almost same as non-clustered index size, we shall consider keeping table in one file group and index on another
  • Organizing tables based on portfolio so that tables can be easily migrated / archived if required.
  • Maintaining some data which is read only along with read write data in same db. In this case we can keep these tables in a separate read only file group

The Solution

The SQL 2017 CTP2 release includes a solution to the above limitation.

The SELECT … INTO syntax for creating the table is now extended to include the filegroup information.
The modified syntax would be like below

SELECT column1, column2,.. columnN
INTO NewTableName
ON FileGroupName 
FROM Table1 t1
JOIN Table2 t2
ON condition…
…

If you see the code above, the highlighted part designates the filegroup to which this new table has to be associated. Only caveat is that the filegroup must pre-exist in the database where the above query is being executed otherwise SQLServer will throw an error

Illustration

Lets see how we can create a new table on the fly and associate it to a newly created filegroup on the database
Steps are as outlined below
1. Adding a new filegroup to existing database Test2017
ALTER DATABASE [Test2017] ADD FILEGROUP [SupplimentaryDataFG] 
GO

 You can check the db properties to ensure this is created as per the below

2. Associating a new file to the newly created filegroup
ALTER DATABASE [Test2017]   
ADD FILE   
(  
    NAME = test1dat3,  
    FILENAME = ‘D:SupplData.ndf’,  
    SIZE = 5MB,  
    MAXSIZE = 100MB,  
    FILEGROWTH = 5MB  
)
TO FILEGROUP [SupplimentaryDataFG]; 
The file will get created as per below
3. Creating a test table on the fly on the newly created filegroup

SELECT * INTO TestTable
ON SupplimentaryDataFG
FROM 
(VALUES
(‘Test’,1),
(‘Foo’,2),
(‘Foobar’,3)

)t(Description,Code)

Checking the table properties to see the filegroup info
Another way to check this is to use the script as option in SSMS. Script as CREATE option for the table will give the below code
USE [Test2017]
GO

/****** Object:  Table [dbo].[TestTable]    Script Date: 6/16/2017 7:09:03 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[TestTable](
[Description] [varchar](6) NOT NULL,
[Code] [int] NOT NULL
) ON [SupplimentaryDataFG]
GO
The highlighted part clearly shows the table to be in the new filegroup we created and associated it to

Conclusion

From the above illustration its pretty evident that we can create a table on the fly and associate it to any filegroup we have pre-created in the database. 
This is a very useful small enhancement which has been introduced in SQL 2017 which helps us to organize the table data into multiple filegroups simultaneously at the time of their creation on the fly using queries. 

References

Published by dataplatformcentral