Today, we are going to see a very simple trick about SQL SERVER Management Studio (SSMS), where we will learn how to rebuild all indexes on the table with a single click. While I considered this as a very simple trick while demonstrating this to my e-commerce client during the Comprehensive Database Performance Health Check, they were very surprised to see this option.
If you want to rebuild all the indexes on any particular table quickly, you can just do that with the help of SSMS. Just go to the table, further expand the indexing folder and right after that you can right-click on it and select the option to rebuild all the indexes. Here is the image for additional clarity.
Once you select that option it will bring up the following screen.
Over here it will display all the indexes of the table and you can just click OK and it will rebuild all the indexes. You can use the same screen to see the various details about indexes. On the same page, you will see the Total Fragmentation as well.
It is quite possible that not every time you want to use SSMS. There are chances when you just want to generate the script and execute that script. In that case, you can use the same screen to generate the script to rebuild all of your indexes as well.
Once you generate the script you can execute them in the SSMS query window.
Well, while the tip looks very simple, trust me not many people know about it. If you know any such tricks, please post them in the query window and I will be happy to post on the blog with the due credit to you. Meanwhile, If you have ever attended any of my online sessions, you might be familiar with my opinion that I do not like more than 7 indexes on any table. You can read more about that in this blog post: SQL SERVER – Poor Indexing Strategies – 10 Don’ts for Indexes
Reference: Pinal Dave (https://blog.sqlauthority.com)