Delete ClarityConnect placeholder records that have been superseded

delete_placeholders.sql

use illuminatedb
go

-- note, it is possible for the placeholder hash to change with new versions of ClarityConnect, replace this where necessary.

select distinct NoteId
into #PublishedNoteIds
from Clarity.NoteVersion NV
  inner join Clarity.PublishedNoteVersion PNV on PNV.NoteVersionId=NV.NoteVersionId AND NV.HeaderHash <> '89C4E5D1-4BC6-2C9F-9754-1270646437D0'

alter table #PublishedNoteIds ADD PRIMARY KEY (NoteID)

-- execute this until no rows are returned
declare @FirstPublished bigint
select top 1 @FirstPublished=NoteVersionId
from Clarity.PublishedNoteVersion PNV
order by NoteVersionId

delete top(1000000) NV
from Clarity.NoteVersion NV
  inner join #PublishedNoteIds PNI on PNI.NoteId = NV.NoteId
where NV.HeaderHash = '89C4E5D1-4BC6-2C9F-9754-1270646437D0'
  and NV.NoteVersionId < @FirstPublished