如何将Azure索引的"auto_created“标志从1更改为0？(与SSDT冲突)

Question

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

在过去几个小时的XEvent跟踪和逐步解析SSDT反汇编之后，我发现原因是SSDT使用此查询查找索引(下面是googleability的重复)。

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

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

更新：我刚找到这条线，看来我不是一个人，但我也不应该期望SSDT会有任何变化，*抱怨*

UPDATE2:奇怪的是，使用WITH AUTO_CREATED = ON 的有效选项。CREATE INDEX浅谈Azure SQL，但不使用ALTER INDEX，*更多的抱怨*：当我尝试它时，我得到了这个错误：

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

Msg 155、级别15、状态1、第1行“AUTO_CREATED”不是公认的ALTER选项。

好奇者的

背景故事：

• 大约2-3年前，自动在我的一个表上创建了一个索引。
  • 名称类似于nci_wi_TableName_ABCDEF...的索引的类型。

• 当创建索引、视图或其他对象时，它将auto_created标志设置为1。
• Visual的Server ( SSDT，又名*.sqlproj)总是排除带有auto_created <> 0的对象，这意味着SSDT没有看到我的索引。
• 你会认为这不会是个问题，因为索引一开始就不在我的项目中，所以没有冲突，对吗？
  • 嗯，在我使用SSMS而不是SSDT将INDEX's定义复制到我的SSDT项目中时，我使用"Script as CREATE .“菜单，并将SSDT表文件中的copying+pasting SQL复制到SSDT表文件中。从那时起，我就一直与SSDT和我的Azure SQL数据库发生冲突--同时我的on和本地开发版本也没有任何问题，因为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'));

Answer 1

并不理想，但您可以删除并重新创建索引(Es)。显然，如果表很大，这可能需要一些时间，所以如果这是一个生产应用程序，您可能不得不将它安排在一个维护期间。如果出于任何原因，您希望保留标志之后，您将需要删除并重新创建，以重新设置它。

正如AMtwo在下面的注释中所建议的:您可以先创建替换索引，然后删除带有标志的索引并重命名新索引(如果在任何查询提示中可能引用原始索引，则重命名非常重要)。这将暂时占用更多的空间，但不会给您留下一个句点，而没有索引覆盖那些列，如果您使用的是活动DB，而不是在维护期间这样做的话，这些索引可能很重要。