Create a table that support soft delete

When we are working on the data ware house fact and dimension, we are not hard deleting the records from the table we are doing soft delete. A Soft Delete means we don’t physically delete the row, instead we logically mark it deleted so it can be restored, audited, or ignored by the app.

Basically, in the soft delete we are not deleting any records just we are updating the flag which indicate that this record is mark as deleted. Maily this type of table implementation we have seen in the data ware house SCD type two (Slowly changing dimension type two) we are having the records history.

Read more about

Slowly changing Dimension in DWH :

https://bageshkumarbagi-msbi.blogspot.com/2022/05/slowly-changing-dimension-in-dwh.html

Implementation of slowly changing Dimension Type-2 Creating a new additional record to maintain the history (Using Start Date and End Date) using SQL Merge statement

https://bageshkumarbagi-msbi.blogspot.com/2022/05/implementation-of-slowly-changing_17.html

let’s see here a small example. For the demo we are creating a table

CREATE TABLE is_Delete_demo_tbl (     Id INT PRIMARY KEY,     CustNm VARCHAR(50),     CustAdd VARCHAR(100),     IsDeleted BIT NOT NULL DEFAULT 0,-- Soft delete flag     DeletedOn DATETIME NULL,     DeletedBy SYSNAME NULL );

Inserting few records in this table

insert into is_Delete_demo_tbl(ID,CustNm,CustAdd) values (1,'Bagesh','Noida'),(2,'Amit','Greater Noida') select * from is_Delete_demo_tbl

See the records.

IsDeleted flag 0 indicate that the record is not deleted.

Let do the soft delete one record.

update is_Delete_demo_tbl set IsDeleted=1, DeletedOn=getdate(), DeletedBy='Bagesh' where id=1 select * from is_Delete_demo_tbl

When we want to use the data always, we need to use below query.

We can create a view to access the active records like

create view vw_ActiveRecords as select * from is_Delete_demo_tbl where IsDeleted=0

If we want to restore Soft Deleted Row then we need to update that records.

update is_Delete_demo_tbl set IsDeleted=0, DeletedOn=null, DeletedBy=null where id=1 select * from vw_ActiveRecords

For the better performance we will create index

CREATE NONCLUSTERED INDEX IX_is_Delete_demo_tbl ON is_Delete_demo_tbl(ID) WHERE IsDeleted = 0;