Tag: T-SQL

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.

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

One of the things I needed to do recently was move some objects from one schema to another. I wrote about moving an object between schemas recently, but another part of that process was finding  the objects to move.

This is a quick post on how to find the objects in a schema. To start, here are a number of objects in a test database.

A schema has a name, which is the way that we would search for related objects. That means I want a parameter for my query, so I’ll start with a variable to store the name. For me, I’ll use a well named variable like this:

DECLARE @schema VARCHAR(100) = 'SallyDev';

Now I have a schema name, where do I find schema data? There is a DMV called sys.schemas, which contains a bit of meta data. If I query that, I see this:

I can see my SallyDev schema, so I know I’ll query this DMV.

The other information I need is the object data, which is in sys.objects. I query that for the various data I want, but I want to limit data by the schema. In sys.objects, there is a schema_id, which is the data I’ll join with from sys.schemas.

When I do that, I build a query like this:

DECLARE @schema VARCHAR(100) = 'SallyDev';

SELECT 
        o.type_desc,
        s.name AS 'Schema Name',
        o.name AS 'Object Name',
        o.object_id

FROM sys.objects o
     INNER JOIN sys.schemas s ON s.schema_id = o.schema_id

WHERE s.name = @schema;

I can execute that and I’ll see the objects I need.

SQLNewBlogger

This was a post related to the one on moving objects and I wrote this write after that one. It was only about 5 minutes longer to put this together, and it gives me a script I can easily search for on my blog if I need to do this task.

Once again, a quick and easy way to show some skills, practice explaining something, and get some knowledge stored for my own reference.