You’ve just discovered that your indexes are fragmented, so you decid to do something about it. You know you need to do something to your indexes, but you’re stuck wondering what the difference is between REBUILD and REORGANIZE.
Well, it’s nice and simple, REBUILD will drop the index and then completely recreate it, where as REORGANIZE will reorganize the leaf nodes within your index.
However, you want to know which one to use and when! Unfortunately, that comes down to personal preference as it’s one of those hot topics that no one can really agree on! But as a guide line if your index fragmentation is between 10% & 40% then REORGANIZE is the option, anything over 40% should use REBUILD.
Here’s some sample T-SQL REORGANIZE and REBUILD code.
ALTER INDEX ALL ON YourSchema.TableName REBUILD
ALTER INDEX ALL ON YourSchema.TableName REORGANIZE