5

How to purge a table with a billion records that has foreign key to another table? Should one partition the table and the child table and use partition swap?

  • Which side of the key is the table on? – Mr.Brownstone Feb 2 '18 at 1:12
  • 3
    As always. – Erik Darling Feb 2 '18 at 1:29
  • 1
    @Krismorte slower than disabling and reenabling the foreign key? Best of luck with that. 😂👌 – Erik Darling Feb 2 '18 at 1:52
  • 3
    @Krismorte oh cool, how do you keep bad data from getting in while your foreign keys are disabled? Or are they untrusted and kinda useless anyway? – Erik Darling Feb 2 '18 at 2:03
  • 1
    @Krismorte you could add an answer with your suggestion. – ypercubeᵀᴹ Feb 2 '18 at 22:12
1

Try this:

  1. Insert 3 month data in new table with similar structure:
declare @CurDate datetime
select * 
into NewTable
from ExistingTable 
where datecol >= @CurDate and datecol <= dateadd(month,3,@CurDate)
  1. Rename old table to anything unique or drop table as per requirement
  2. Rename new table to existintg table
  3. Insert into NewChildTable with similar structure which match New parent table of Step 3
  4. Rename or drop existing child table
  5. Rename new child table to existing child table
|improve this answer|||||
0

My suggestion

1) Save the create script of the all Foreign keys involved

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) Dropped

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) Execute the delete like this to avoid log growing

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) Finally recreate all foriegn keys

Just remember to run set rowcount 0 after star a new script in the same session

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.