Sql-Server
使用 BOTH 堆和聚集索引修復 SQL 表
不知何故，我們有一個 SQL Server 表，表上有一個HEAP 和一個 CLUSTERED 索引。有沒有辦法來解決這個問題？例如，通過對象 ID 刪除 HEAP 索引？
如果我們刪除聚集索引，它將創建第二個 HEAP，在重新創建聚集索引時將刪除該 HEAP。這個殭屍 HEAP 索引將保留。
抱歉，表格中的最後一項是針對不同的對象/表格的。
452964740 != 562309263. 因此，您在同一個表中沒有堆和聚集索引。解釋
堆是沒有聚集索引的表。
參考：堆（沒有聚集索引的表）
所以你不能在一個表中同時擁有一個堆和一個聚集索引。
系統索引
sys.indexes 的定義如下：
Column name Data type Description ------------------- ------------------- --------------------------------------------------------------------------- object_id int ID of the object to which this index belongs. name sysname Name of the index. name is unique only within the object. NULL = Heap index_id int ID of the index. index_id is unique only within the object. 0 = Heap; 1 = Clustered index; > 1 = Nonclustered index type tinyint Type of index: 0 = Heap 1 = Clustered 2 = Nonclustered 3 = XML 4 = Spatial 5 = Clustered xVelocity memory optimized columnstore index (Reserved for future use.) 6 = Nonclustered columnstore index type_desc nvarchar(60) Description of index type: HEAP CLUSTERED NONCLUSTERED XML SPATIAL CLUSTERED COLUMNSTORE (Reserved for future use.) NONCLUSTERED COLUMNSTORE is_unique bit 1 = Index is unique. 0 = Index is not unique. data_space_id int ID of the data space for this index. Data space is either a filegroup or partition scheme. 0 = object_id is a table-valued function. ignore_dup_key bit 1 = IGNORE_DUP_KEY is ON. 0 = IGNORE_DUP_KEY is OFF. is_primary_key bit 1 = Index is part of a PRIMARY KEY constraint. is_unique_constraint bit 1 = Index is part of a UNIQUE constraint. fill_factor tinyint > 0 = FILLFACTOR percentage used when the index was created or rebuilt. 0 = Default value is_padded bit 1 = PADINDEX is ON. 0 = PADINDEX is OFF. is_disabled bit 1 = Index is disabled. 0 = Index is not disabled. is_hypothetical bit 1 = Index is hypothetical and cannot be used directly as a data access path. Hypothetical indexes hold column-level statistics. 0 = Index is not hypothetical. allow_row_locks bit 1 = Index allows row locks. 0 = Index does not allow row locks. allow_page_locks bit 1 = Index allows page locks. 0 = Index does not allow page locks. has_filter bit 1 = Index has a filter and only contains rows that satisfy the filter definition. 0 = Index does not have a filter. filter_definition nvarchar(max) Expression for the subset of rows included in the filtered index. NULL for heap or non-filtered index.如果你這樣加入
sys.indexes，你會得到更好的結果sys.objects：select so.name, si.* from sys.indexes as si join sys.objects as so on si.object_id = so.object_id您可以在此語句的基礎上建構並加入
sys.schemas以檢索表的（模式）所有者等。參考資料：