Thursday , March 28 2024

Moving all Nonclustered Indexes to Another Filegroup

On one of our customer we had to move all nonclustered indexes to another filegroup and we have found a solution for it and I wanted to share with you.

Actually if you have not too much indexes on your database you can go your all indexes one by one and get create script with DROP_EXISTING=ON option and you can create your indexes on another filegroup. if you dont know about DROP_EXISTING option, when you create your indexes with this option, SQL Server will create new index and delete the old one. So if you change ON PRIMARY to ON ANOTHERFILEGROUP on your index creation script with this option you will move your indexes.

However in many systems there are too much nonclustered indexes and sometimes that may be hard to seperate pk and nonclustered indexes. Below script have helped me alot.

Move All Nonclustered Index to Another Filegroup

This script will give the current create script of all nonclustered indexes in your database. Dont forget to change ON PRIMARY to ON AnotherFilegroup and if you want you can add ONLINE=ON,MAXDOP=10 or something else,FILLFACTOR=90. Generally I am using them.

Here is the link where I have found this create script.

https://www.mssqltips.com/sqlservertip/3441/script-out-all-sql-server-indexes-in-a-database-using-tsql/

Loading

About dbtut

We are a team with over 10 years of database management and BI experience. Our Expertises: Oracle, SQL Server, PostgreSQL, MySQL, MongoDB, Elasticsearch, Kibana, Grafana.

2 comments

  1. Hi, you have mentioned everything about moving non clustered indexes to Another Filegroup very descriptively. This post is very informative and helpful for me specially and for other also thankyou so much for share with us.

Leave a Reply

Your email address will not be published. Required fields are marked *

Categories