2

Is there a way to automatically move clustered indexes A on one filegroup, and all nonclustered indexes to a different filegroup B upon DDL creation? We have developers constantly creating tables/indexes, and would like a way to automate this in dev environment. We have an old SAN Hard drive, and in some cases separating indexes on different filegroups may optimize performance, according to our DBA.

https://docs.microsoft.com/en-us/sql/relational-databases/databases/database-files-and-filegroups?view=sql-server-2017

New contributor
Martin Smith51 is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.
  • Clustered index is just a well arranged table. When we create clustered index on any table it just arrange data based on key column. – Rajesh Ranjan Feb 11 at 6:53
  • try to use hyperlinks instead of direct links – Muhammad Waheed Feb 11 at 7:30
  • Creating an Index is related to a Table, which may be part of one file group only. Now, creating nonclustered indices for each table automatically is your question? if you are using `auzre' refere old post – MarmiK Feb 12 at 14:22
2

Clustered index is just a well arranged table. When we create clustered index on heap, it arrange data of the tables based on key column. So moving clustered index means you are moving table to different file group.

There’s no inbuilt feature in SQL Server to automatically move indexes to specific file group when we create index, instead we can write index maintenance script to move indexes to specific file group based on index type or we'll have to specify file group while we are creating indexes.

  • interesting, how would I write the index maintenance script? – Martin Smith51 Feb 11 at 7:03
  • Will share script – Rajesh Ranjan Feb 11 at 7:54
  • 2
    >>>So moving clustered index means you are moving table to different file group<<< If you'll try to use CREATE INDEX ... ON<ANOTHER_FG> syntax it won't move LOB data to ANOTHER_FG – sepupic Feb 11 at 9:03
  • yes, I would like to have it automatically move it to appropriate filegroup when writing ddl statement – Martin Smith51 Feb 11 at 15:49

Your Answer

Martin Smith51 is a new contributor. Be nice, and check out our Code of Conduct.

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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