Should I delete Hypothetical Indexes?

Question

I have noticed that Hypothetical indexes exist in a certain database. I have searched around and it appeared that this type of indexes are created by Tuning Advisor and are not always deleted.

There are several topics including official documentation of how to clear/delete these indexed, but I was not able to find if these indexes have any impact to the server themselves.

What I have check using the script below is that there is no size information about them:

SELECT OBJECT_NAME(I.[object_id]) AS TableName
      ,I.[name] AS IndexName
      ,I.[index_id] AS IndexID
      ,8 * SUM(A.[used_pages]) AS 'Indexsize(KB)'
FROM [sys].[indexes] AS I
INNER JOIN [sys].[partitions] AS P 
    ON P.[object_id] = I.[object_id] 
    AND P.[index_id] = I.[index_id]
INNER JOIN [sys].[allocation_units] AS A 
    ON A.[container_id] = P.[partition_id]
WHERE I.[is_hypothetical] = 1
GROUP BY I.[object_id]
        ,I.[index_id]
        ,I.[name]
ORDER BY 8 * SUM(A.[used_pages]) DESC

and having them, I have decided to check if there are some usage information about them in order to leave these who are often used, but again nothing was return. (I have use the "Existing Indexes Usage Statistics" from this article).

Could anyone tell why keeping these indexes is wrong and if I can define which of them should be kept?

Answer 1

Just delete them, they aren't actually taking up any space or causing any performance hit/benefit at all, but if you're looking at which indexes are defined on a table and forget to exclude hypothetical indexes, it might cause some confusion, also in the unlikely event that you try to create an index with the same name as one of these indexes, it will fail as it already exists.

Answer 2

Just USE the database you want to clean and run this:

DECLARE @sql VARCHAR(MAX) = ''

SELECT
    @sql = @sql + 'DROP INDEX [' + i.name + '] ON [dbo].[' + t.name + ']' + CHAR(13) + CHAR(10)
FROM 
    sys.indexes i 
    INNER JOIN sys.tables t 
        ON i.object_id = t.object_id 
WHERE 
    i.is_hypothetical = 1


EXECUTE sp_sqlexec @sql

Answer 3

If you use custom schemas and checked analyzing indexing views, you need some further improvements to the above scripts:

DECLARE     @sql VARCHAR(MAX) = ''

SELECT      @sql = @sql 
            + 'DROP INDEX [' + i.name + ']'
            + 'ON [' + OBJECT_SCHEMA_NAME(t.[object_id]) + '].[' + t.name + ']' 
            + CHAR(13) + CHAR(10)
FROM        sys.indexes i 
INNER JOIN  sys.[all_objects] t 
ON          i.object_id = t.object_id 
WHERE       i.is_hypothetical = 1

PRINT       @sql

EXECUTE     sp_sqlexec @sql