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.
SQL Server – Automatically Place Nonclustered Indexes in Different Filegroup
filegroupsperformancesql serversql-server-2016
Related Question
- Sql-server – general memory requirements for sql server 2008 r2
- SQL Server – Efficiently Recreating Clustered Index
- SQL Server Permissions – Allow Everything Except Overwriting or Creating Database
- How to Force Users to Specify a Filegroup When Creating Tables/Indexes in SQL Server
- SQL Server Filegroups – Cannot Remove Filegroup with No Files
- SQL Server OLTP Filegroups – Optimizing Lookup Tables
- Sql-server – Making production data accessible to developers via masking
Best Answer
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.
Also, this link will help, https://stackoverflow.com/questions/55187160/automatically-create-indexes-in-different-filegroup-edit-publish-profile-script