Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

I haven’t spent a lot of time with savepoints, but I did find a question recently and thought I’d take a moment to dig into how they work. They are interesting, and they can be useful for you in certain situations.

Warning: Anything involving transactions can be tricky, so be sure you test, test, test and check out how things work with a wide variety of situations, including some you might not expect.

Here’s a basic setup. I’ll create a table to log some actions.

CREATE TABLE TransLogger

(ID INT IDENTITY(1,1) NOT NULL CONSTRAINT TransLoggerPK PRIMARY KEY

, LogMessage VARCHAR(200)

)

GO

Now that I have a table, let’s do something in a transaction. I’ll start a transaction, make two inserts, but set a savepoint between them

BEGIN TRANSACTION
INSERT dbo.TransLogger (LogMessage) VALUES ('First insert inside transaction')
SAVE TRANSACTION Firstsave
ROLLBACK TRANSACTION Firstsave
COMMIT
SELECT top 10
  *
  FROM dbo.TransLogger AS tl

If I look at the results, I see this:

That makes sense. I inserted this row (I’ve been testing, so that’s why it’s 11), and marked a savepoint with the SAVE TRANSACTION Firstsave line. Then I rollback a transaction to this savepoint, which does nothing. Finally I commit. I see my one row.

Let’s add something. I’ll add a second item, and decide to roll it back.

DECLARE @rollback INT = 1
BEGIN TRANSACTION
  INSERT dbo.TransLogger (LogMessage) VALUES ('First insert inside transaction')
   SAVE TRANSACTION Firstsave
  INSERT dbo.TransLogger (LogMessage) VALUES ('Second insert inside transaction')
   IF @rollback = 1
     ROLLBACK TRANSACTION Firstsave
COMMIT
SELECT top 10
  tl.ID, tl.LogMessage
  FROM dbo.TransLogger AS tl

Note I’ve added a variable so I can decide to rollback or not. I’d often have some condition or error handling that might cause a rollback, so this simulates that. Note that work before the savepoint is committed, but work after is removed with the ROLLBACK TRANSACTION Firstsave.

My results are a single row. Note, I cleared the table between runs.

Savepoints give me a place to commit work if I need it before doing more. This potentially allows me to capture some changes and not others if I don’t want to fail my entire transaction.

Personally, if I’m doing this, I would likely just have two transactions if I can have one commit without the other.

SQLNewBlogger

A few minutes of experimenting gave me a quick post. I need to do more, and certainly test more, but this is a basic idea of what savepoints are. You can write something similar.