The other day, I received an email from user and after a long time before I answer the question, I had to check the answer online.

Here is the question -

I want to create a new table based from old table, but when I execute following script it gives me an error. Is there anything I am missing in my syntax?

SELECT * 
INTO NewTableName ON MyFileGroup
FROM MyOldTableName

I faintly remember that this was not possible in earlier version of SQL Server but I was not sure if this feature was added in the recent versions or not. I quickly tried few syntaxes and referred online documentation and learned that it is still not possible in the latest version of SQL Server.

The alternative is to just go ahead and change the default filegroup of any new table with following script.

Though, I do not like change the default filegroup for new tables. It is possible that when I have changed the default filegroup some other code executes behind the scene by automated system or my colleague, it will be also created on new filegroup.

ALTER DATABASE DatabaseName
MODIFY FILEGROUP NameofFileGroup DEFAULT

The reason this feature is not supported is that SELCT INTO is minimally logged operation. I seriously hope that some day in the future this feature get added in.

Reference: Pinal Dave (http://blog.sqlauthority.com)