My colleague Steve emailed me today and asked "why are there 214 indices on the table SA_OrderHeader?"

I laughed, and told him don't be silly. SQL Server Enterprise Studio showed 'only' 22. But Steve was using SQL Manager 2008 for SQL Server and saw the strangely named indices below:

Intrigued, I did an sp_help SA_OrderHeader and now I could see them...hypothetical indices!

So, I did some reading about this, and it turns out these are created by the Index Tuning Wizard, and are normally removed when it runs to completion, but I guess sometimes not... see here for instance.

I fiddled around and came up with a way to get rid of them all...

SELECT 'drop index [' + OBJECT_NAME(i.object_id) + '].[' + i.name + ']' 
FROM    sys.indexes i
WHERE   i.is_hypothetical = 1

Run this command and you will get a result set like this:

drop index [II_WarehouseItemLocation].[_dta_index_II_WarehouseItemLocation_5_14869553__K2_K3_1]
drop index [SA_Customer].[_dta_index_SA_Customer_5_142049875__K1_23]
drop index [SA_Customer].[_dta_index_SA_Customer_5_142049875__K2]
drop index [SA_Customer].[_dta_index_SA_Customer_5_142049875__K1]
drop index [SA_Customer].[_dta_index_SA_Customer_5_142049875__K2_K1]
drop index [SA_Carrier].[_dta_index_SA_Carrier_5_259518191__K3]
drop index [SA_Carrier].[_dta_index_SA_Carrier_5_259518191__K1]

...

Paste this back into the command window and run it, and now the evil hypothetical indices are banished!