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.