How can I change the Azure SQL INDEX's "auto_created" flag from 1 to 0? (It conflicts with SSDT)

Question

I've been experiencing a longstanding issue where SSDT will think a table index in my Azure SQL database doesn't exist, when it actually does.

After the past few hours of digging through XEvent traces and stepping-through SSDT disassembly I found out the reason is because SSDT looks for indexes with this query (repeated below for googleability).

The query has the predicate [i].[auto_created] = 0.

I want to keep the index rather than recreate it - is there any way for me to set sys.indexes.auto_created = 0 for a particular index somehow?

---

UPDATE: I just found this thread, looks like I'm not alone, but I shouldn't expect any changes to SSDT either, *grumble*

---

UPDATE2: Curiously, using WITH AUTO_CREATED = ON is a valid option for CREATE INDEX on Azure SQL but not for ALTER INDEX, *more grumbling*: when I tried it I got this error:

ALTER INDEX IX_StarTrekTngBestMoments ON dbo.GeneRoddenberryFanClub SET ( AUTO_CREATED = OFF );

Msg 155, Level 15, State 1, Line 1

'AUTO_CREATED' is not a recognized ALTER INDEX option.

---

Backstory for the curious:

• About 2-3 years ago the Azure SQL Performance Analyzer automatically created an index on one of my tables.
  • The kind of indexes with a name like nci_wi_TableName_ABCDEF....
• When the Azure SQL Performance Analyzer creates an index, view, or other object it sets the auto_created flag to 1.
• Visual Studio's SQL Server Design Tools (SSDT, aka *.sqlproj) always excludes objects with auto_created <> 0, which mean SSDT didn't see my index.
• You would think that wouldn't be a problem because the index wasn't in my project in the first place, so no conflict, right?
  • Well, no - at the time I used SSMS, not SSDT, to copy the INDEX's definition into my SSDT project - by using the "Script index as CREATE to..." menu and copying+pasting the SQL into my SSDT table file. And ever since then I've been having the conflicts with SSDT vs. my Azure SQL database - while also not having any problems with my on-prem and local dev versions because SSDT created those without the auto_created flag.
• I filed a support ticket with Azure support over this in January 2021 - the support request moved very slowly, but by March 2021 they told me they "will be sending out the action plan shortly" (whatever that means), but I haven't heard anything since... Shame, Microsoft, Shame.

I was hoping there might be an option in SSDT for it to not ignore auto_created objects, but unfortunately the SQL query it uses to enumerate indexes in the database has the predicate term hard-coded:

SELECT * FROM (
SELECT * FROM (
SELECT DISTINCT
    SCHEMA_NAME([o].[schema_id]) AS [SchemaName]
   ,[i].[object_id]        AS [ColumnSourceId]
   ,[o].[name]             AS [ColumnSourceName]
   ,[o].[type]             AS [ColumnSourceType]
   ,[i].[index_id]         AS [IndexId]
   ,[i].[name]             AS [IndexName]
   ,[f].[type]             AS [DataspaceType]
   ,[f].[data_space_id]    AS [DataspaceId]
   ,[f].[name]             AS [DataspaceName]
   ,CASE WHEN exists(SELECT 1 FROM [sys].[columns] AS [c] WITH (NOLOCK) WHERE [c].[object_id] = [o].[object_id] AND  [c].[is_filestream] = 1) THEN
            [ds].[data_space_id]
        ELSE
            NULL
        END  AS [FileStreamId]
   ,[ds].[name]            AS [FileStreamName]
   ,[ds].[type]            AS [FileStreamType]   
   ,[i].[fill_factor]      AS [FillFactor]    
   ,CONVERT(bit, CASE [i].[type] WHEN 1 THEN 1 WHEN 5 THEN 1 ELSE 0 END) 
                           AS [IsClustered]
   ,[i].[is_unique]        AS [IsUnique]
   ,[i].[is_padded]        AS [IsPadded]
   ,[i].[ignore_dup_key]   AS [DoIgnoreDuplicateKey]
   ,[t].[no_recompute]     AS [NoRecomputeStatistics]
   ,[t].[is_incremental]   AS [DoIncrementalStatistics]
   ,[i].[allow_row_locks]  AS [DoAllowRowLocks]
   ,[i].[allow_page_locks] AS [DoAllowPageLocks]
   ,[i].[is_disabled]      AS [IsDisabled]
   ,[i].[filter_definition]
                           AS [Predicate]
   ,[i].[compression_delay] AS [CompressionDelay]
   ,CONVERT(bit, ISNULL(INDEXPROPERTY([i].[object_id], [i].[name], N'IsOptimizedForSequentialKey'), 0)) AS [DoOptimizeForSequentialKey]
   ,CONVERT(bit, CASE WHEN [ti].[data_space_id] <> [i].[data_space_id] THEN 0 ELSE 1 END)
                           AS [EqualsParentDataSpace]
   ,[i].[type]             AS [IndexType]
   ,[i].[auto_created]     AS [AutoCreated]
  ,CONVERT(BIT, CASE WHEN [hi].[object_id] IS NULL THEN 0 ELSE 1 END) AS [IsHash]
   ,[hi].[bucket_count] AS [BucketCount]
FROM 
    [sys].[indexes] AS [i] WITH (NOLOCK)
    INNER JOIN [sys].[objects]           AS [o]  WITH (NOLOCK) ON [i].[object_id] = [o].[object_id]
    LEFT  JOIN [sys].[data_spaces]       AS [f]  WITH (NOLOCK) ON [i].[data_space_id] = [f].[data_space_id]
    LEFT  JOIN [sys].[stats]             AS [t]  WITH (NOLOCK) ON [t].[object_id] = [i].[object_id] AND [t].[name] = [i].[name]
    LEFT  JOIN [sys].[tables]            AS [ta] WITH (NOLOCK) ON [ta].[object_id] = [i].[object_id]
    LEFT  JOIN [sys].[data_spaces]       AS [ds] WITH (NOLOCK) ON [ds].[data_space_id] = [ta].[filestream_data_space_id]
    LEFT  JOIN (SELECT * FROM [sys].[indexes] WITH (NOLOCK) WHERE [index_id] < 2) AS [ti] ON [o].[object_id] = [ti].[object_id]
    LEFT OUTER JOIN [sys].[hash_indexes] AS [hi] WITH (NOLOCK) ON [hi].[object_id] = [i].[object_id] AND [hi].[index_id] = [i].[index_id]
WHERE 
    ([o].[type] = N'U' OR [o].[type] = N'V')
    AND [i].[is_primary_key] = 0
    AND [i].[is_unique_constraint] = 0
    AND [i].[is_hypothetical] = 0
    AND [i].[name] IS NOT NULL
    AND [i].[auto_created] = 0
    AND ([o].[is_ms_shipped] = 0 AND NOT EXISTS (SELECT *
                                        FROM [sys].[extended_properties]
                                        WHERE     [major_id] = [o].[object_id]
                                              AND [minor_id] = 0
                                              AND [class] = 1
                                              AND [name] = N'microsoft_database_tools_support'
                                       ))) indexBase
WHERE [IndexType] NOT IN (3, 4, 5, 6)
) AS [_results] ORDER BY ColumnSourceId,IndexId  OPTION (USE HINT('FORCE_LEGACY_CARDINALITY_ESTIMATION'));

Answer 1

Not ideal, but you could drop and recreate the index(es). Obviously this could take some time if the table is large so you may have to schedule it for a maintenance period if this is a production application. And if for any reason you want to keep the flag afterwards you'll need to drop and recreate again to reset it.

As suggested by AMtwo in comments below: you could create the replacement index first, then drop the one with the flag and rename the new one (renaming is important if the original index might be referred to in any query hints). This will take more space temporarily, but won't leave you with a period while there is no index covering those columns which could be important if you are working with an active DB rather than doing this in a maintenance period.