Soft delete - use the IsDeleted flag or a separate table of tables?

Question

Should we use a flag for soft deletions or a separate carpentry table? Which is more efficient? The database is SQL Server.

Background Information

While we have a database consultant, and take a look at our database schema. When we gently delete a record, we update the IsDeleted flag in the corresponding tables. It has been suggested that instead of using the flag, save the deleted entries in a separate table and use the join, as that would be better. I put this proposal to the test, but at least on the surface, an extra table and join look more expensive than using a flag.

Initial testing

I installed this test.

Two tables, an example and a remote example. I added a non-clustered index to the IsDeleted column.

I performed three tests, downloading a million records with the following deleted / not deleted relationships:

• Deleted / Uninstalled
• 50:50
• 10/90
• 1/99

Results - 50/50

Results - 10/90

Results - 1/99

Database scripts, for reference, example, deletion, and example for example .IsDeleted

CREATE TABLE [dbo].[Example]( [ID] [int] NOT NULL, [Column1] [nvarchar](50) NULL, [IsDeleted] [bit] NOT NULL, CONSTRAINT [PK_Example] PRIMARY KEY CLUSTERED ( [ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO ALTER TABLE [dbo].[Example] ADD CONSTRAINT [DF_Example_IsDeleted] DEFAULT ((0)) FOR [IsDeleted] GO CREATE TABLE [dbo].[DeletedExample]( [ID] [int] NOT NULL, CONSTRAINT [PK_DeletedExample] PRIMARY KEY CLUSTERED ( [ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO ALTER TABLE [dbo].[DeletedExample] WITH CHECK ADD CONSTRAINT [FK_DeletedExample_Example] FOREIGN KEY([ID]) REFERENCES [dbo].[Example] ([ID]) GO ALTER TABLE [dbo].[DeletedExample] CHECK CONSTRAINT [FK_DeletedExample_Example] GO CREATE NONCLUSTERED INDEX [IX_IsDeleted] ON [dbo].[Example] ( [IsDeleted] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO 

Answer 1

The numbers you seem to indicate that my initial impression was correct: if your most common query for this database is to filter on IsDeleted = 0 , then performance would be better with a simple bit flag, especially if you use indexes wisely .

If you often request deleted and recovered data separately, you can see the performance gain due to the presence of a table for deleted items and another for recovered items with the same fields. But denormalizing your data like this is rarely a good idea, as it will often cost you a lot more in code maintenance costs than it will increase performance.

Answer 2

I am not an SQL expert, but, in my opinion, it all depends on the frequency of database usage. If the database is accessed by a large number of users and it should be effective, then using a separate isDeleted table will be good. The best option would be to use the flag during production time, and as part of the daily / weekly / monthly maintanace, you can move all soft deleted records to the isDeleted table and clear the soft deleted record production table. A mixture of both will be a good good.