2

Is it possible to overwrite the same disk address on a TSQL update statement? A use case would be preventing recovery of the original text on disk for security or anonymity reasons.

CREATE TABLE Test(
  Id int, 
  Message nvarchar(1000))
GO

INSERT INTO Test (Id, Message) VALUES (1, 'Hello!')
GO

So at this point, 'Hello!' is written to disk. If an update statement is ran, can it be guaranteed (or at least highly likely) to overwrite the same location on disk?

UPDATE Test SET Message = '000000' WHERE Id = 1
GO

In the SQL Server implementation, what is the probability that the original 'Hello!' value will no longer be on disk?

I'm assuming the new value would be the same size. My guess is that if the new value were of a different size, then SQL Server would write the updated value to a new location, leaving the 'Hello!' in the original location, but now marked as free. I also assume that a delete does nothing to the original value on disk, but just marks the location as free.

If this is not the case, or not guaranteed, or at least highly-likely, Is there another way to remove the value from disk?

The requirement here is not to the level of keeping government secrets safe. It's more of a marketing claim that deleted items are truly gone and can't be recovered.

I understand there are a lot of caveats here. I know the SQL language does not address this. I'm asking specifically about SQL Server's implementation. The answer could be that it's unknowable. Just wondering if there's documentation, or common knowledge, or a way to test implementation specifics.

Francisco d'Anconia
  • 2,436
  • 1
  • 19
  • 25
  • 1
    There are no guarantees. I am guessing that for compliance purposes, deleting from the database and all backups is sufficient, unless you have business processes that regularly scrape disks for deleted information. (I'm not a lawyer, so you should really check with your!) – Gordon Linoff Apr 24 '21 at 18:19
  • 2
    I too would suggest that the *real* question here isn't about whether the same disc sectors is overwritten, but if the overwriting of the data in the database is enough to meet compliance/legal requirements. That isn't a question for [so] but a question for [law.se]. – Thom A Apr 24 '21 at 18:34
  • That, again, depends on the wording on the compliance you are trying to adhere to, @Franciscod'Anconia , and that isn't a question for [so]. – Thom A Apr 24 '21 at 18:42
  • I’m voting to close this question because the question is about compliance with legal requirements; specifically if an `UPDATE` statement would comply with them. As such this is a question for [law.se], not [so]. – Thom A Apr 24 '21 at 18:44
  • 1
    You could investigate and test this easily enough, create a new blank database, insert your sample data, detatch it, open file with any free hex editor and locate your data; reattach and update, then check in hex editor again. – Stu Apr 24 '21 at 19:00
  • @Larnu The question is not about legal compliance. The question is, is there any documentation, or any way to demonstrate how SQL Server implements this. Stu mentions a reasonable/helpful approach for example. – Francisco d'Anconia Apr 24 '21 at 19:09
  • 1
    Then if you're asking about how SQL Server manages its data and files, you should be asking on [dba.se]. – Thom A Apr 24 '21 at 19:13
  • 2
    I have a suspicion the data probably won't be overwritten and the the storage engine will allocate the updated data separately and mark the old data in the global allocation map (I think?) as free. I agree this is not a question for SO specifically, but would be interested in the outcome nonetheless. – Stu Apr 24 '21 at 19:14
  • 3
    The data is not overwritten until something else needs that space. Use `sp_clean_db_file_free_space` to delete it – Charlieface Apr 24 '21 at 21:43

0 Answers0