Does SQL's DELETE statement truly delete data?

Question

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.

Question: 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!

Answer 1

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

Answer 2

SQL Server just marks the space of deleted rows as available, but does not reorganize the database and does not zero out the freed up space. Try to "Shrink" the database, and the deleted rows should no longer be found.

Thanks, gbn, for your correction. A page is the allocation unit of the database, and shrinking a database only eliminates pages, but does not compact them. You'd have to delete all rows in a page in order to see them disappear after shrinking.