Plateforme Level Extreme
Abonnement
Profil corporatif
Produits & Services
Support
Légal
English
An esoteric little SQL brain teaser
Message
De
20/10/2013 13:34:08
 
 
À
20/10/2013 13:23:50
Information générale
Forum:
Microsoft SQL Server
Catégorie:
Autre
Versions des environnements
SQL Server:
SQL Server 2012
Application:
Web
Divers
Thread ID:
01585872
Message ID:
01585886
Vues:
54
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.
Précédent
Suivant
Répondre
Fil
Voir

Click here to load this message in the networking platform