Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
1  
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‌​. –  Aaron Bertrand Oct 28 '12 at 16:35
    
You CAN do that in tue UI. You know, you can recreate a clustered index in the UI, too. –  TomTom Oct 28 '12 at 18:05
1  
@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. –  Aaron Bertrand Oct 28 '12 at 22:57
    
SQL, in general, is not something to use (professionally) without reading the documentation. It likely is one of the most learning intensive areas, also because of high specific customization needs. –  TomTom Oct 9 '13 at 13:58
add comment

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

This question came from our site for professional and enthusiast programmers.

1 Answer

up vote 4 down vote accepted

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

http://gallery.technet.microsoft.com/scriptcenter/c1da9334-2885-468c-a374-775da60f256f

Shows a script for this.

http://www.mssqltips.com/sqlservertip/2442/move-data-between-sql-server-database-filegroups/

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.