Level Extreme platform
Subscription
Corporate profile
Products & Services
Support
Legal
Français
An esoteric little SQL brain teaser
Message
From
20/10/2013 13:34:08
 
 
To
20/10/2013 13:23:50
General information
Forum:
Microsoft SQL Server
Category:
Other
Environment versions
SQL Server:
SQL Server 2012
Application:
Web
Miscellaneous
Thread ID:
01585872
Message ID:
01585886
Views:
56
So it works like "mark records for deletion" and defers the "pack" until the commit?

I've actually been struggling the last two minutes between typing, "well, sort of"....and "well, not really" :)

When SQL Server deletes a row (even in a txaction), it's not so much marked - in the txaction log, there are 2 system tables (INSERTED/DELETED), available inside a trigger, to see the state of the row before/after the DML statement (in this case, a DELETE). The txaction manager works in conjunction with a transaction log. (This is probably why newer relational engines, like those in Azure, require each table to have a PK)

I realize you didn't mention FoxPro, but since the concept of "mark records for deletion" and the VFP DELETED() function are obviously part of all our vocabulary - SQL Server doesn't have a SET DELETED OFF or a filter capability on what's DELETED(). So it's a bit of apples and oranges (part of why I struggled years ago in the learning curve to SQL). Some people who move from VFP to SQL wind up creating a DELFLAG column for each table, and prefer to use that, because they're initially more comfortable with the behavior.

I think the more accurate thing to say is that SQL Server has logged the location for the data pages to be de-allocated - and refers to those addresses if a rollback occurs. My understanding is that the disk space is "protected" during that stage, though I don't know if I'd want to rely on that.
Previous
Next
Reply
Map
View

Click here to load this message in the networking platform