I must choose between three possible DELETE methods in SQL Server. The table: RawData stays at around 500GB.
Here is the table:
CREATE TABLE dbo.rawData ( rowId INT IDENTITY PRIMARY KEY, AreaId INT, MeasureId INT, someData UNIQUEIDENTIFIER DEFAULT NEWID(), DateEnergy DATETIME DEFAULT GETDATE(), addedBy VARCHAR(30) DEFAULT SUSER_NAME(), ts ROWVERSION ) GO CONSTRAINT [PK_RawData] PRIMARY KEY CLUSTERED ([AreaId] ASC, [MeasureId] ASC, [DateEnergy] ASC) CREATE INDEX IX_RawData_AreaId ON RawData (AreaId); GO CREATE NONCLUSTERED INDEX [IX_RawData_DateEnergy] ON [dbo].[RawData] ([DateEnergy]) INCLUDE ([AreaId],[MeasureId]) GO CREATE NONCLUSTERED INDEX [IX_RawData_MeasureId_DateEnergy] ON [dbo].[RawData] ([MeasureId],[DateEnergy]) INCLUDE ([AreaId])
Method 1: Add a column IsDeleted type boolean.
- Would require that this column be indexed as well (exclude rows deleted by the SELECT)
- Update this boolean column (update = rowlock)?
Method 2: Add a new table that will store rows that need to be deleted
- Outer join between the two tables for the SELECT
- No update necessary on RawData
Method 3: Use an existing column to detect rows to be deleted
- Update the AreaID to a negative value to mark it for deletion (update = rowlock)?
- Use the existing suppression system that removes rows by batches of 10000
The deletion might need to be cancelled at any given time
Which method would be the most efficent?
Are there any other methods not mentionned that might be better?
Update July 11th 2019
The number of rows being deleted depends on their collection date. So there could be millions or hundreds or none.