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 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 2

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.