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.
