Fun with Savepoints–#SQLNewBlogger

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:

2018-11-12 16_46_08-SQLQuery8.sql - Plato_SQL2017.sandbox2 (PLATO_Steve (57))_ - Microsoft SQL Serve

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.

2018-11-12 16_49_42-SQLQuery8.sql - Plato_SQL2017.sandbox2 (PLATO_Steve (57))_ - Microsoft SQL Serve

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.

About way0utwest

Editor, SQLServerCentral
This entry was posted in Blog and tagged , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.