Information générale
Forum:
Microsoft SQL Server
Versions des environnements
SQL Server:
SQL Server 2012
>>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.
>
>Didn't mean to really parallel it with VFP but I was referring to the idea that actuall truncation (which I didn't even know you could do in a transaction as I find programmatic truncation kind of scary anyway as I just haven't found a use case where I thought it was the best thing to do) was deferred in some way - i.e. the table would be treated as truncated within the context of the transaction but that would not be a fact in storage until the transaction was committed.
>
> I just figured it was done by elves, or magic or something. Trying to understand it more deeply than that (when I know guys Iike you I can ask if I really need to know <g> ) just makes my head hurt.
>
>I am spending the day trying to master Angular, Bootstrap, Breeze, underscore, require, WebAPI, EF and mocks and repository patterns.
>
>Tomorrow I will seriously consider developing a drug habit.
That's a good point about why would anyone want to truncate a table within a transaction to begin with.
That's a busy Sunday ;-) -- "master Angular, Bootstrap, Breeze, underscore, require, WebAPI, EF and mocks and repository patterns."
I watched the Browns game yesterday since they were on TV playing the Packers. It was good to see that they have gotten a lot more respectable. I don't care what team it is, I hate to see a team down and out year after year.
Précédent
Suivant
Répondre
Voir le fil de ce thread
Voir le fil de ce thread à partir de ce message seulement
Voir tous les messages de ce thread
Voir tous les messages de ce thread à partir de ce message seulement