主鍵、聚集索引和分區
我們儲存財務記錄的數據，將其分成 4 個表。我對這裡的設置最適合什麼感到有點茫然，或者至少應該朝哪個方向前進，因為我收到了很多相互矛盾的建議和阻力，並且沒有所有事實可以決定前進的道路。下面基本上是所有 4 個表的樣子。
CREATE TABLE [Example].[MasterRecordTable]( [rowDateTime] [datetime] NOT NULL CONSTRAINT [rowDateTime] DEFAULT (GETDATE()), [recordID] [int] IDENTITY(1,1) NOT NULL, [date] [date] NOT NULL, [field1] ..., ... CONSTRAINT [PK_MRT] PRIMARY KEY CLUSTERED ( [recordID] ASC, [date] ASC ) CREATE TABLE [Example].[ChildTables]( [recordID] [int] IDENTITY(1,1) NOT NULL, [date] [date] NOT NULL, [field1] ..., ... CONSTRAINT [PK_CT] PRIMARY KEY CLUSTERED ( [recordID] ASC, [date] ASC )
- 所有表都使用包含
recordIDand的聚集主鍵date，即使在處理過程中我們不會加入或包含date在 WHERE 子句中，它只是recordID需要作為主鍵。對我來說date不應該出現在主鍵或聚集索引中。- 所有表都帶有該
date欄位，如果您考慮我在下面關於歸檔/分區的問題，這是否必要？它出現在所有表格中的唯一原因是為了協助我們的手動歸檔過程。- 我們不使用外鍵，我想知道這是否值得重新考慮
- 我們需要一個歸檔策略。目前我們有與上述結構相同的表，
_Archive添加到名稱並放置在單獨的文件組和硬碟驅動器中。然後，我們每天手動將WHERE date <= @aYearAgo每個表的記錄移動到它的_Archive對應位置。這以及開發橋接兩個表的查詢都是乏味且耗時的。我們正忙於評估分區，我想得到一些消息靈通的答案，以了解考慮到上面的表結構什麼設置是理想的。根據date值進行分區和歸檔是有意義的。我們希望能夠將舊數據移至逐漸變慢的硬碟驅動器，然後在 5 年後刪除。- 單獨使用聚集主鍵不是更好嗎
recordID，分區欄位 (date) 是否必須是聚集索引的一部分？- 我們唯一需要的時間
date是在這些表格上報告時。根據您上面的答案，我想這可以通過主表上的 NC 索引來完成，然後通過 recordID 連接到子表。如果有任何不清楚的地方，請告訴我。這是我腦海中的一個轉儲，我渴望與在索引、鍵關係和分區方面知識淵博的人合作。謝謝！
在處理過程中，我們不會在 WHERE 子句中加入或包含日期
當查詢謂詞中未指定分區列時，將需要觸及所有分區。設身處地為 SQL Server 著想——在不知道分區列值的情況下如何知道要訪問（或不訪問）哪些分區？考慮沒有分區消除的單例索引查找將查找每個分區。與非分區表相比，當返回的行數很少時，這種查找成本會更加明顯。不過，全掃描不會有太大差異。
單獨在recordID上有一個聚集主鍵不是更優化嗎，分區欄位（日期）是否必須是聚集索引的一部分？
是的，單獨的 recordID 會更優化，但分區列必須是聚集索引鍵以及所有唯一索引的一部分。這意味著主鍵和唯一約束必須將分區列作為鍵的一部分。
只有 recordID 需要作為主鍵。對我來說，日期不應該出現在主鍵或聚集索引中。
在不知道您的數據的情況下，我不能說日期是否應該是主鍵的一部分。假設不是從數據模型的角度來看，沒有它就無法對主鍵索引進行分區。考慮到對查詢的性能影響，一種方法是使用非集群非分區主鍵。
用於不經常訪問的數據的分層儲存是具有不同文件組的表分區的案例。您仍然需要開發一種方法來物理移動基礎文件或將行從一個文件組物理移動到另一個文件組。單獨的分區
SWITCH不能完成這項工作。
主鍵應該只是 ID 欄位。
應該有一個超過“日期”的索引，因為您將它用於歸檔和報告。我什至會考慮將其設為聚集索引。
如果只有一個應用程序對數據庫具有寫入權限並且不允許使用者進行手動更改，則外鍵不是必須的。
分區功能對歸檔沒有用處，我建議改為執行自動化作業。擺脫手動過程，人類會犯錯誤。
不要編寫連接工作表和存檔表的查詢，使用視圖。