Does a SQL Server update nvarchar statement overwrite the same address on disk if the new value is the same size? Is it possible to overwrite the same disk address in a T-SQL 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 run, 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 if someone has tested implementation specifics.
We're using a hosting service that doesn't encrypt the SQL Server disk. We do encrypt the value before writing to the db. Just looking into a 'belt and braces' solution.
There are so many aspects to this, but the short answer is that you have to expect that old values can be on disk (as this is from what I understand what the question boils down to).
So, this falls under the same category as below question, with the same answer:
Q: Can we protect from a Windows admin to be able to do anything inside a SQL Server and see all data in a SQL Server?
A: No. But you can try to make sure that what they read appear as garbage to them. I.e., a suitable encryption implementation.
If you are truly interested in this, I suggest you first read up on the storage architecture of SQL Server. Without that, you won't be able to make heads of tails of the answers. I.e., make sure you understand database files, pages, transaction logging, heaps, B-trees etc.
The update can be done in place, on the same page. That goes for an index as well. If you modify the key of an index column then the row has to move (DELETE/INSERT internally). If the row doesn't fit, then you get a forwarded record or a page split. Another factor is that after a delete, (and an update can be delete/insert, remember), the old row will initially still be there as a ghost row. But even after that, the page isn't &quot;compacted on delete&quot;, i.e., the storage reclaim is done when the &quot;free&quot; space is needed at some later time.
And then you have transaction logging.
And backups.
If you have versioning enabled, then old row versions are in tempdb and cleared up when not needed, after some time.
And the added complexity how other, perhaps less basic, technologies affects the picture. I'm thinking of things such as:

Columnstore indexes
Memory optimized tables
Change Data Capture
Replication
HA solutions such as Availability Groups
And whatever more I could come up with given some more time to think about this

I think you get the picture by now. No, you can't claim that the old data is gone. Encryption might be an important component to this, depending on your particular situation.