Store table in separate filegroup in SQL Server

Question

I have big SQL Server database, with a lot of blob data in some tables.

For performance optimization, I want to move one table to separate hard drive.

I created new filegroup, and "row data" file.

But, I can't change any parameters in table properties: they are all disabled.

And I don't know a way to enable it.

Can anybody help me?

enter image description here

I don't know that there's a way to do this in the UI - you need to rebuild the clustered index and point it at the new filegroup. You'll need to repeat for non-clustered indexes as well IIRC. You can use CREATE INDEX ... ON newfilegroup WITH (DROP_EXISTING = ON). See the documentation‌.
You CAN do that in tue UI. You know, you can recreate a clustered index in the UI, too.
@TomTom thanks, what I meant was that you can't necessarily just say "move this table to that filegroup" by clicking a button. You have to know the mechanics behind it in order to just use the UI in some cases - for example, if your clustered index is the primary key, which is quite common, the Filegroup option in the UI is disabled. Unless you know that the OP's clustered index is not involved in any constraints, it's tough for you to argue that they can do this easily with the UI.

TomTom · Answer 1 · 2012-10-28 18:04:51Z

Create / recreate the CLUSTERED INDEX. It's leaves are the data.

Shows a script for this.

has a step by step guide with Pictures. And more usefull scripts all around filegroups.

Both Show up when you ask "SQL move table filegroup" in Google.

migrated from stackoverflow.com Oct 28 '12 at 19:02