Should I drop and create indexes on my tables that SQL Server created?

Question

In an effort to get rid of some fragmentation left from rebuilding and defraging we thought that we would drop and create indexes so I went to write a script.

It identifies a clustered index that needs work and drops indexes and primary keys and rebuilds the indexes and primary keys for a table.

Here is the problem I ran into: SQL Server makes quite a bit of its own indexes based on statistics with its own unique naming system.

Question: Do I want to only drop and create the indexes we made or do I want to drop all indexes and create only the ones that we made or do we want to drop all indexes including the ones SQL Server made and create all indexes including the ones that SQL Server made?

Answer 1

You can always defrag your indexes, which is easier than drop and recreate. There's a decent explanation of how to do it in this article.