Sql – Does SQL’s DELETE statement truly delete data


Story: today one of our customers asked us if all the data he deleted in the program was not recoverable.

Aside scheduled backups, we shrink the log file once a day, and we use the DELETE command to remove records inside our tables where needed.

Though, just for the sake of it, I opened the .mdf file with an editor (used PSPad), and searched for a particular unique piece of data -I was sure- was inside one of tables.

Problem: I tracked it in the file, then executed the DELETE command, and it was still there.

Is there a particular command we are not aware of to delete the records physically form the disk?

Note: we know there are particular techniques to recover lost data from the hard drives, but here I am talking about a notepad-wannabe!

Best Solution

The text may still be there, but SQL Server has no concept of that data having any structure or being available.

The "freed space" is simply deallocated: not removed, compacted or zeroed.

The "Instant File Initialization" feature relies on this too (not zeroing the entire MDF file) and previous disk data is still available eben for a brand new database:

Because the deleted disk content is overwritten only as new data is written to the files, the deleted content might be accessed by an unauthorized principal.

Edit: To reclaim space:

ALTER INDEX...WITH REBUILD is the best way

DBCC SHRINKFILE using NOTRUNCATE can compact pages into gaps caused by deallocated pages, but won't reclaim space in a page for deleted row