IN Select into we can not specify file
group and hence always table was created primary file group.
Then we have to create clustered index
on other filegroup to change filegroup of created tabled.
This is time and resource consuming as it needs to move data from one filegroup to other filegroup.
But now in MS SQL 2017 we can
specify file group while using select into.
Lets see how to do this.
We have two filegroups in database.
[PRIMARY] and [SAMPLE]
So I want to create table in SAMPLE
file group.
Select * into
newtable on
[SAMPLE] from sys.tables
This will create table in SAMPLE file group instead of
PRIMARY.