Voice of the DBA

Tag: syndicated

Get a Range of Sequence Values: #SQLNewBlogger
I discovered a procedure recently that I wasn’t aware of: sp_sequence_get_range. This post looks at how the proc works.

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

The Setup

I have a sequence object, IDCounter, that is an integer with an increment of 2. The next value that is returned is shown here:

The next value returned will be 99 (increment by 2).

However, imagine that I know I need 10 new values. I don’t want a loop to get these values. Instead, I want to move the sequence to 10 values ahead.

These ten values will be 99, 101, 103, 105, 107, 109, 111, 113, 115, 117. The current value should then be 117 if we get 10 rows.

Let’s use sys.sp_sequence_get_range to do this. I’ll use this code:
```
DECLARE @i SQL_VARIANT
EXEC sys.sp_sequence_get_range @sequence_name = N'dbo.IDCounter', @range_size = 10, @range_first_value = @i OUTPUT
SELECT @i AS RangeStart
```
I need to use a SQL_VARIANT as the output, though I can cast this to anything once I have the value.

When I run this code, notice the output.

Now if I check the metadata, I’ll see the current value below as 117.

There are client side applications that gather a bunch of data and know they need to insert xx rows. This helps them both update the sequence and also reserve these values for themselves. Of course, if the application fails, these values might be lost.

SQL New Blogger

A quick post. This took me about 5 minutes to test and about 10 minutes to structure a quick post on something I learned.

As a follow-up, I’ll use another post to show how this works in an application that reserves these values and then another application performs an insert.

You could easily do this on your blog and show some knowledge.
April 22, 2026
Monday Monitor Tips: Oracle Custom Metrics

One of the popular features of Redgate Monitor has been the ability to add custom mertics for various purposes. This has been popular, and along with our curated list at sqlmonitormetrics.com, it’s a feature that’s used by many.

It has been a SQL Server only function for awhile, but customers have requested other platforms. We’ve added the capability for Oracle and PostgreSQL.

This is part of a series of posts on Redgate Monitor. Click to see the other posts.

Adding Custom Metrics

You can see on my custom metric config page that I have a few business metrics added. I recommend these to customers as often a business metric catches an issue before it appears in Perfmon type data. Let’s add a new metric. I’ll click the

I’ll click the “Add a custom metric on the right side” and will get a new form.

This form used to be mostly the same, but the “Choose where to collect data from” didn’t have a drop down. It only had the 1. Select instances and the 2. Select databases (to the right). This new drop down is where you pick the platform.

If I click the drop down, I see three choices: SQL Server, Oracle, and PostgreSQL.

I can select Oracle and my select database section disappears. This is because an instance and a database are the same thing in Oracle.

If I select PostgreSQL, I can still select databases.

The rest of the form works the same way. When I’ve selected Oracle, I get a default query listed that lets me know the structure of my result set. I need to return an integer with the con_id, a metric value that is a scalar and an optional string with details.

From there things work as they do with other metrics.

Summary

This post shows a new capability in Redgate Monitor for Oracle, where you can track your own data, as you see fit, and then let Redgate Monitor alert on changes, graph this over time, and help you better understand your system.

We are enhancing and adding to Redgate Monitor every week, but there are always more things that many of you want, and custom metrics give you the ability to choose what you want to monitor.

Redgate Monitor is a world class monitoring solution for your database estate. Download a trial today and see how it can help you manage your estate more efficiently.

April 20, 2026
JSON_OBJECTAGG is an Aggregate: #SQLNewBlogger

I wrote an article recently on the JSON_OBJECTAGG function, but neglected to include an example of why it can behave weirdly. This post looks at something you might not realize unless you remember this is an aggregate function.

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

A Strange Result

I have some data, which I show below. Some teams, cities, and years.

If I ask for this data in a JSON_OBJECTAGG query, I get one document back. Which is fine. You can see this below

However, if I were to add the year separately as a column, I get an error without a GROUP BY. This shows me this is an aggregate, which I read and knew, but wasn’t thinking about in my testing:

If I add a GROUP BY, then I see these results. Notice that I get documents for each section of the GROUP BY with just that data. Each of these documents is 1 team, except for the 1960 year, where three teams started.

If you are using this to assemble simple JSON documents with a WHERE clause, you might not realize this is an aggregate and behaves that way.

Make sure you test your code in a few ways and treat this function as an aggregate.

SQL New Blogger

This post took a little longer to write (20 minutes). I took the code from my previous article, but I restructured a few things once I realized how this works as an aggregate. It’s a simple look at the function, but it also points out something I learned, which isn’t necessarily obvious. Talking about that shows you have some knowledge besides just copy/pasting code to create JSON documents.

You could do this as well, and leverage the idea of building off one post to show something in another.

April 15, 2026
Impactful Sessions I’ve Seen: T-SQL Tuesday #196

This month I’m thrilled that Steve Hughes is hosting. I’ve read this Data on Wheels blog for a long time and I appreciate all that he does, especially given his struggles with ALS. I kicked myself a few times for not asking him to host earlier, but I’m glad I finally did.

I also was honored to meet him a few weeks ago and spend a little time with him.

With that, let’s get to Steve’s invitation.

Lasting Memories from Events

These days I don’t go to a lot of sessions at events. It’s not that I can’t learn from others, or be inspired, but I find myself often viewing the time spent with others as more valuable. I can (and often do) work through articles, tutorials, and other resources online. For me, the time talking to others if more valuable than sitting in sessions.

However, I wasn’t always like that. Early on in my career, and even throughout, I went to a lot of sessions to learn things. Over the years, a few things have stood out to me. There is some recency bias here, and I’m sure I’m forgetting about some great sessions along the way. I don’t always remember titles, so forgive if I’ve made a mistake.

Tempdb in Early SQL Server – This was at the very first PASS Summit in Chicago, where Kalen Delaney have a talk on tempdb. I remember this because we had lots of contention in tempdb on SQL Server 6.5 and I was struggling. Finding solutions was one of the main reasons I got my boss to pay for the conference. I know I learned a few things and worked to restructure some queries after this. I even got to ask Kalen a question, which was a thrill for me.

Reporting Services CI – I think this was a Nigel Sammy session, but it might have been Roy Ernest or another friend. I was working to get DevOps style checks for SQL code at the time and I wondered how someone would do this for SSRS. It was an interesting session as it reaffirmed a lot of my guesses for how to approach this, but taught me an interesting way to try and verify parts of reports while ignoring others. The session focused on ensuring your clients were happy with report data more than visual layout.

The BI Power Hour – I went to a few of these, but the first one was with Donald Farmer, Euan Garden, and others at TechEd in 2003 or 2004. It was wildly entertaining and I appreciated the humorous approach to showing ways to use the BI platform. While I am not quite as entertaining, I try to add a few minor funny things in sessions.

Basic Backup for SQL Server – Sean McCown gave this at a few SQL Saturdays. I am not sure if I saw him in Dallas or elsewhere, but I think I was sitting in the room and decided not to go anywhere. Instead, I was curious what Sean might cover that I didn’t know. While I knew most of the material, there were a few tweaks in settings that made a different to performance. This session reminded me there is always more to learn, even when you think you know a topic well.

Clustering in 60 Minutes – Early in the SQL Server Central days, Brian was doing a lot of HA for his company. He presented this session at a PASS Summit where he built a SQL Server 7 or 2000 cluster live onstage in 60 minutes. It was cool to see and it reminded me that I should practice my demos and make them smooth.

Live Encryption Changes – Simon Sabin gave an interesting talk at SQL Bits where he showed a live app running against his database and how he could implement encryption without breaking the app. The app just ran a workload against his database in a loop. He made changes to the db in the background without causing errors, which was cool. I enjoyed learning the encryption techniques, but more this inspired my Architecting Zero Downtime Deployments, where I wrote my own app to do this.

There are plenty more, but I’ll stop here.

There’s always a lot to learn, and going to sessions is a great way to get inspired and help you build your own solutions.

April 14, 2026