如何将 Azure SQL INDEX 的“auto_created”标志从 1 更改为 0？（与 SSDT 冲突）

我一直遇到一个长期存在的问题，SSDT 会认为我的 Azure SQL 数据库中的表索引不存在，而实际上它确实存在。

在过去几个小时挖掘 XEvent 跟踪和逐步完成 SSDT 反汇编之后，我发现原因是因为 SSDT 使用此查询查找索引（在下面重复以获取 googleability）。

查询有谓词[i].[auto_created] = 0。

我想保留索引而不是重新创建它 - 有什么方法可以让我以sys.indexes.auto_created = 0某种方式设置特定索引吗？

更新：我刚刚找到这个线程，看起来我并不孤单，但我也不应该期望 SSDT 有任何变化，*grumble*

UPDATE2：奇怪的是， using是Azure SQL 上WITH AUTO_CREATED = ON 的有效选项，CREATE INDEX但不是ALTER INDEX，*更多抱怨*：当我尝试它时，我收到了这个错误：

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

消息 155，第 15 级，状态 1，第 1 行

'AUTO_CREATED' 不是公认的 ALTER INDEX 选项。

好奇的背景故事：

• 大约 2-3 年前，Azure SQL 性能分析器自动在我的一张表上创建了索引。
  • 名称类似的索引类型nci_wi_TableName_ABCDEF...。
• 当 Azure SQL 性能分析器创建索引、视图或其他对象时，它会将auto_created标志设置为1.
• Visual Studio 的 SQL Server 设计工具（SSDT，又名*.sqlproj）总是排除带有 的对象auto_created <> 0，这意味着 SSDT 没有看到我的索引。
• 你会认为这不是问题，因为索引一开始就没有在我的项目中，所以没有冲突，对吧？
  • 嗯，不 - 当时我使用 SSMS，而不是 SSDT，将INDEX's 的定义复制到我的 SSDT 项目中 - 通过使用“脚本索引作为创建到...”菜单并将 SQL 复制+粘贴到我的 SSDT 表文件中. 从那以后，我一直在与 SSDT 与我的 Azure SQL 数据库发生冲突——同时我的本地和本地开发版本也没有任何问题，因为 SSDT 创建了没有auto_created标志的版本。
• 我在 2021 年 1 月向 Azure 支持提交了一份支持票 - 支持请求进展非常缓慢，但到 2021 年 3 月，他们告诉我他们“将很快发送行动计划”（无论这意味着什么），但我没有从那以后听到任何消息......耻辱，微软，耻辱。

我希望 SSDT 中可能有一个不忽略auto_created对象的选项，但不幸的是，它用于枚举数据库中索引的 SQL 查询具有硬编码的谓词术语：

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'));