No it's not some ultra misterious indexing feature that will make your database fly like it's on steroids.
Hypothetical indexes and database objects in general are simply objects created by DTA (Database Tuning Advisor)
Their names look like this:
| Indexes |
_dta_index_* |
| Statistics |
_dta_stat_* |
| Views |
_dta_mv_* |
| Partition functions |
_dta_pf_* |
| Partition schemes |
_dta_ps_* |
* = some autogenerated name
If you're going to implement the recommendations given by the DTA then do rename the objects to avoid confusion.
Checking if an index is hypothetical is simple. Just run this query:
SELECT *
FROM sys.indexes
WHERE is_hypothetical = 1
