How to append filegroup dynamically while creating an index in SQL Server?

Question

I have to move some tables from primary file group to the default prime group. I have 3 different environments and in each environment name of the default file group is different. Thus, I can fetch the default file group name as:

select name from sys.filegroups where is_default=1;

Now while recreating my indexes I want to use the output of the above query to specify the file group where I want to create index(as default file group has different names in different environments, thus, I want only a single query to be promoted). I tried as:

CREATE UNIQUE CLUSTERED INDEX PK_INDEX
ON slam.MY_TABLE(COL_1)
WITH (DROP_EXISTING=ON) ON [select name from sys.filegroups where is_default=1];

But I am facing below error:

Msg 1921, Level 16, State 1, Line 19 Invalid filegroup 'select name from sys.filegroups where is_default=1' specified.

Any help will be much appreciated.

By default, any table or index you create will be stored in the default filegroup. You don't need to do anything special here - just remove the FG part. — SMor
– SMor, Commented Nov 29, 2018 at 13:09
The issue is a new filegroup has been created which has been marked as default..the tables and indexes were created before the creation of filegroup. I just want to shift the tables from PRIMARY file group to the newly created default file group — codeLover
– codeLover, Commented Nov 29, 2018 at 13:33

Andrey Nikolov · Accepted Answer · 2018-11-29 09:09:30Z

1

You need to use dynamic sql for that. Select the name of your default file group, construct your query and execute it like this:

declare @fg_name nvarchar(250), @sql nvarchar(max);
select @fg_name = name from sys.filegroups where is_default=1;
set @sql = N'CREATE UNIQUE CLUSTERED INDEX PK_INDEX
    ON slam.MY_TABLE(COL_1)
    WITH (DROP_EXISTING=ON) ON [' + @fg_name + N'];';
exec sp_executesql @sql;

answered Nov 29, 2018 at 9:09

Andrey Nikolov

13.5k3 gold badges26 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

codeLover Over a year ago

There is no way to do it in a single query?

Andrey Nikolov Over a year ago

I don't think so. You can combine constructing the query and selecting the file group name into single query, though. But why do you want to be a single query?

codeLover Over a year ago

As I am not sure whether dynamic sql qill be allowed to me or not as per organizational standards

Andrey Nikolov Over a year ago

OK, then why not just omit the file group name. It should go to the default.

codeLover Over a year ago

No it is not going

|

Collectives™ on Stack Overflow

How to append filegroup dynamically while creating an index in SQL Server?

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related