Does SQL DELETE statement really delete data?

Question

History : today one of our customers asked us if all the data that he had deleted in the program was deleted.

In addition to the planned backups, we shrink the log file once a day, and we use the DELETE command to delete records inside our tables, where necessary.

Although, just for the sake of this, I opened the .mdf file with an editor (using PSPad) and searched for a specific unique piece of data - I was sure - was inside one of the tables.

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

Question : Is there any specific command that we don’t know about to delete records physically from disk?

Note: we know that there are special methods for recovering lost data from hard drives, but here I am talking about using notepad!

Answer 1

The text may still be present, but SQL Server has no idea which data is structured or available.

The "free space" is simply freed up: it is not removed, it is not compacted, and it is not zeroed.

" " Initialization of the instant file "also uses this function (does not reset the entire MDF file), and the data from the previous disk is still accessible by eben for the new database:

Since the contents of the remote disk are overwritten only as new data is written to the files, an unauthorized director may contact the remote content.

Edit: To make room:

ALTER INDEX ... WITH REBUILD - The Best Way

DBCC SHRINKFILE using NOTRUNCATE can combine pages at intervals caused by freed pages, but will not free up page space for the deleted row

Answer 2

SQL Server simply marks the deleted row space as available, but does not reorganize the database or zero out the freed space. Try Compressing the database and deleted rows will no longer be found.

Thank you, gbn, for your correction. A page is a unit of database distribution, and shrinking a database eliminates only pages, but does not compress them. You will need to delete all lines on the page so that they disappear after reduction.

Answer 3

If your client is concerned about data security, he should use Transparent Database Encryption . Even if you destroy the information from the table, the record is still in the log. Even when the journal is being processed, the information is still in backups.

Answer 4

You could write update with dummy values ​​before issuing delete , thereby overwriting the data on the disk before the database marks it as free. (However, this also works with LOB fields, however, it would justify).

And, of course, you will still have a problem with the logs and backups, but I suppose you already solved them.