index not exists

Question

After removing the indexes from the table, using the sys.indexes query, the removed indexes are still displayed. Although the deleted indexes do not exist in the table index folder.

If this query is Join with sys.partitions, we will have the output of the number of Object Explorer indexes.

I thought the problem was with SSMS, but the output in Sqlcmd also returned 16 records as in the first image.

Answer 1

Hi @MohammadAmin Moazami

In addition to the above two answers, you can also refer to the following links.

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/b98ae1f2-a147-441a-91b1-1e657ec49341/hypothetical-indexes?forum=sqldatabaseengine

https://stackoverflow.com/questions/21111927/should-i-delete-hypothetical-indexes

Best regards,

Percy Tang

---

If the answer is the right solution, please click "Accept Answer". If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Check the flag is_hypothetical in sys.indexes. This looks like indexes that the Database Tuning Advisor has considered, but which has not been actually created.

Answer 3

Those are not real indexes. They are generated statistics and "hypothetical" indexes created by DTA.

See:

https://blog.sqlauthority.com/2019/02/28/sql-server-how-to-drop-all-the-hypothetical-indexes-of-database/