刪除具有外鍵約束的大表的最佳方法

如何清除具有指向另一個表的外鍵的十億條記錄的表？應該對錶和子表進行分區並使用分區交換嗎？

嘗試這個：

1. 在具有類似結構的新表中插入 3 個月的數據：

declare @CurDate datetime
select * 
into NewTable
from ExistingTable 
where datecol >= @CurDate and datecol <= dateadd(month,3,@CurDate)

2. 根據要求將舊表重命名為任何唯一或刪除表
3. 將新表重命名為現有表
4. 插入具有與步驟 3 的新父表匹配的類似結構的 NewChildTable
5. 重命名或刪除現有子表
6. 將新子表重命名為現有子表

我的建議

1）保存所有涉及的外鍵的創建腳本

SELECT distinct 'alter table '+rcu.TABLE_SCHEMA+'.'+rcu.TABLE_NAME+' add constraint '+rc.CONSTRAINT_NAME+' foreign key ('+rcu.COLUMN_NAME+') references '+rcu1.TABLE_SCHEMA+'.'+rcu1.TABLE_NAME+'('+rcu1.COLUMN_NAME+')'
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc
INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE rcu ON rc.CONSTRAINT_CATALOG = rcu.CONSTRAINT_CATALOG AND rc.CONSTRAINT_NAME = rcu.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE rcu1 ON rc.UNIQUE_CONSTRAINT_CATALOG = rcu1.CONSTRAINT_CATALOG AND rc.UNIQUE_CONSTRAINT_NAME = rcu1.CONSTRAINT_NAME
where rcu.TABLE_NAME='YOUR_TABLE'

2. 掉落

SELECT distinct 'alter table '+rcu.TABLE_SCHEMA+'.'+rcu.TABLE_NAME+' drop constraint '+rc.CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc
INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE rcu ON rc.CONSTRAINT_CATALOG = rcu.CONSTRAINT_CATALOG AND rc.CONSTRAINT_NAME = rcu.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE rcu1 ON rc.UNIQUE_CONSTRAINT_CATALOG = rcu1.CONSTRAINT_CATALOG AND rc.UNIQUE_CONSTRAINT_NAME = rcu1.CONSTRAINT_NAME
where rcu.TABLE_NAME='YOUR_TABLE'

3）像這樣執行刪除以避免日誌增長

declare @var bigint, @var2 bigint, @var3 bigint
set @var = (select count_big(1) from [Your_Database]..[YOUR_TABLE](nolock) --where filter if it need
set @var2 = 0
set @var3 = 1
while @var2 <> @var
begin
set rowcount 500000
   delete from [Your_Database]..[YOUR_TABLE] --where filter if it need
   set @var2 = @var2+@@ROWCOUNT
   set @var3 = @var3+1
end

4）最後重新創建所有外鍵

請記住在同一會話中為新腳本加註星標後執行 set rowcount 0

引用自：https://dba.stackexchange.com/questions/196862