Does CHECKPOINT work the same as COMMIT in TSQL?

Question

I normally use fully-explicit transactions in my stored procs (BEGIN TRANSACTION .... COMMIT). I just ran across an old one that instead uses "CHECKPOINT" at certain places in the code.

This won't do the same thing, right?? Even if the database is in simple mode, the whole thing will still run as one big transaction, even with a bunch of CHECKPOINTS stuck in the middle?

Answer 1

Checkpoint just writes dirty pages to disk, not at all the same thing. http://msdn.microsoft.com/en-us/library/ms188748.aspx

Answer 2

No.

CHECKPOINT is different and independent to commit/rollback.

This article demonstrates "SQL Server 2000 I/O Basics" (still Ok for SQL Server 2005 etc).

• You can checkpoint and write to disk but subsequently rollback

• The BEGIN/COMMIT is nothing to do with recovery model (full, simple, bulk logged). The TXN must either complete or fail as one.

• In case of, say power failure, the data may on disk because of a CHECKPOINT but uncommitted so is rolled back as part of the database startup.

In the olden days, it was used to try and "empty" the log of committed entries for the simple recovery model. CHECKPOINT discarded them. For FULL recovery model, you'd need BACKUP LOG.