SQL Server Update NVARCHAR Statement Disk Address Overwrite

disk-spacesql serverstoraget-sqlupdate

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 if someone has tested implementation specifics.

Best Answer

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 "compacted on delete", i.e., the storage reclaim is done when the "free" space is needed at some later time.

And then you have transaction logging.

And backups.

And the added complexity how other, perhaps less basic, technologies affects the picture. I'm thinking of things such as:

Columnstore indexes
Memory optimized table
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.

Related Solutions

SQL Server 2008 R2 – Disk Size Considerations

You will need exactly the same disk space for your mirrored (secondary) database as for the primary database. They are the same and the secondary database will have the same growth rate as your primary database and should be provisioned the same.

SP_SPACEUSED will show you how much space your primary database is now using (you should make it larger and not rely on autogrowth), and you can grab growth information from the default trace to give you some limited information about how often the database has grown, you can then infer the growth rate from the autogrowth parameters.

DECLARE @path NVARCHAR(260);

SELECT 
   @path = REVERSE(SUBSTRING(REVERSE([path]), 
   CHARINDEX('\', REVERSE([path])), 260)) + N'log.trc'
FROM    sys.traces
WHERE   is_default = 1;

SELECT 
   DatabaseName,
   [FileName],
   SPID,
   Duration,
   StartTime,
   EndTime,
   FileType = CASE EventClass 
       WHEN 92 THEN 'Data'
       WHEN 93 THEN 'Log'
   END
FROM sys.fn_trace_gettable(@path, DEFAULT)
WHERE
   EventClass IN (92,93)
ORDER BY
   StartTime DESC;

PostgreSQL – Does Updating a Row with the Same Value Actually Update the Row?

Due to the MVCC model of Postgres, and according to the rules of SQL, an UPDATE writes a new row version for every row that is not excluded in the WHERE clause.

This does have a more or less substantial impact on performance, directly and indirectly. "Empty updates" have the same cost per row as any other update. They fire triggers (if present) like any other update, they have to be WAL-logged and they produce dead rows bloating the table and causing more work for VACUUM later like any other update.

Index entries and TOASTed columns where none of the involved columns are changed can stay the same, but that is true for any updated row. Related:

It's almost always a good idea to exclude such empty updates (when there is an actual chance it may happen). You did not provide a table definition in your question. We have to assume first_name can be NULL (which wouldn't be surprising for a "first name"), hence the query has to use NULL-safe comparison:

UPDATE users
SET    first_name = 'Michael'
WHERE  id = 123
AND    first_name IS DISTINCT FROM 'Michael';

If first_name IS NULL before the update, a test with just first_name <> 'Michael' would evaluate to NULL and as such exclude the row from the update. Sneaky error. If the column is defined NOT NULL, use the simple equality check, though, that's a bit cheaper.

Best Answer

Related Solutions

SQL Server 2008 R2 – Disk Size Considerations

PostgreSQL – Does Updating a Row with the Same Value Actually Update the Row?

Related Question