SQL Server Performance Forum – Threads Archive

Specifying a file group

Is there a way to specify a particular filegroup for the table to be created in when using a statement like the following: SELECT * FROM TABLE_A
INTO TABLE_B

I think it will always be created in the default filegroup. But you can specify a custom default filegroup. ———————–
–Frank
http://www.insidesql.de
———————–

When you create TABLE_B, use ON filegroup_name option.
Luis Martin
Moderator
SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

i.e., use CREATE TABLE.. followed by INSERT INTO.. SELECT.., as opposed to SELECT INTO.., which is generally bad practice and can cause issues with locking in tempdb..sysobjects.
Tom Pullen
DBA, Oxfam GB

If you specify to create on a specific filegroup the table will be created, as it specifies the filegroup on which the table is stored. If filegroup is specified, the table is stored in the named filegroup. The filegroup must exist within the database. If DEFAULT is specified, or if ON is not specified at all, the table is stored on the default filegroup. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

]]>