What happens when we delete a record?

gbianchi · April 6th, 2009, 07:59 AM

No no. Why? The records are in fact deleted. If you do a commit (if you are in a transaction), the database will forget about them forever. The space is there, probably physically the records too, but is like when you delete something from the OS, the file is gone, but the only thing that OS does is killing the pointer. Here is the same problem, but since you are using a file, it will just keep the space for itself.

joxa83 · April 6th, 2009, 08:14 AM

I have asked "Is row deleted from page or just marked as deleted?" and Old pedand answered "The record truly is deleted." I think we should ask him what he meant when he said "truly". I mean truly = "record is still in data file but it is not logically in table - like you said forget about pointer" or truly = "no record at all in data file"

gbianchi · April 6th, 2009, 08:18 AM

Probably (and almost sure) the record is deleted, but still in the file, but no way you can recover it.

joxa83 · April 6th, 2009, 09:05 AM

I agree, that is very logical and it explains my experiment :)

Jeff Moden · April 6th, 2009, 09:27 AM

Deleting rows will not cause the space used by a table to be recovered or returned. Even if you try to shrink the DB, it may not shrink at all unless you tell the server to move the data to the beginning of the file. And, in full recovery mode, even that may not work until you do a backup. If replication is active, you may also have to run it and then do the backup.

Old Pedant · April 7th, 2009, 04:07 PM

Yeah, it's database dependent. Some DBs actually will recover space and hand it back to the file system. Others don't.

A lot depends on what the underlying file system is capable of. For example, that file (the ".mdf" file) is now 3MB. But even though the data is deleted, there will still be a lot of "housekeeping" info in the DB. (Schema, if nothing else. Page pointers, etc.) And the Windows operating system does *NOT* provide any way for a program to release individual pages of any file. Other operating systems do, and so on them the individual pages could be released. Put it another way: Windows doesn't support "sparse" files. That is, if you write 4KB to offset 0 in a file and then ask to seek to position 1MB and write another 4KB, Windows *will* create a file that is 1MB+4KB long. Again, other operating systems *could* create a file only 8KB long (or, if they have fixed size pages of, say, 64KB then the file might be 128KB long).

I once created a Windows-based Object Oriented Database system. And indeed I had this problem. I had no way to release file space back to the system. But what I *could* do was mark pages (64KB pages, to match the size of Windows memory pages) as "free" and then go reuse them rather than extending the size of the file when new data was added. And I'm sure that's exactly what SQL Server does.

Now, it's trivial to write a program that "compacts" a database that contains "holes" (free space), and of course Access has a builtiin utility that will do this. One can understand why SQL Server doesn't make it this simple, because in order to do so one has to do it while the system remains "live", available to other users. Still, I don't think it would be that hard to do. You just have to use a multi-phase commit process. (Which is what I did for that OODBMS; if a page was changed by user1, a *NEW* copy of the page was created in the file and only when the new pages was completely written did I change the page map to point to it. And any users who still had a read-only copy of the old page map would still get the old page. And only when no users were referring to the old page map would the old page be finally marked as deleted and available for reuse. So a relatively simple two-phase commit system. I could get away with that because we did DB locking on a page by page basis, not record by record. I'd bet SQL Server uses something much more sophisticated than that. Multi-phase commit, no doubt.)

For what it's worth, if file system space recovery were important (and of course it's not, on today's disks!), you could break the single MDF file down into many smaller files. Perhaps one file that contained the schema info, another with the "page map", and then "extents" that contained the actual data pages. Now, when an "extent" is empty, it's easy to release it to the system by simply deleting the file. Shades of MVS/360 from 1968 or so! But hey, a *big* disk back then was maybe 20 megabytes. Recovery of file system space was critical! (Yes, MVS used the term "extents.")

****

OOPS...That's OS/MVT in 1968. MVS didn't appear until 1974.