I saw a question asking about the next sequence value and decided to try and answer it myself. I assumed this would be easy, and it was, but I used some AI help to make it very quick to get the value and learn something.
Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.
Checking for Sequences
I know I’ve done some testing lately with sequences for a customer, so I decided to ask Prompt AI to help. I connected to a database and asked this question after clicking ALT+Z.
I got this response, which I executed before accepting the code. One thing I like about SQL Prompt is I can execute code and then adjust my prompt (or paste in an error). As you can see, I have 3 sequences in this database. I get a bunch of the meta data, including the current_value.
However, is that the next value or the last one? It could be interpreted multiple ways. I could look this up, but I decided to ask again. I accepted the code, then wrote a quick SQL statement to get the next value. If you’ve never done this, you might not know how sequences work, which is a different issue. In my case, a “se” tab, “ne” tab “i” got e this code. I then asked to find this.
UPDATE: From this article, the current_value has an issue. For new sequences, it shows the starting value, even if that value hasn’t been used, so the last_used_value is a better choice.
SQL Prompt AI returned this query, which gets me the current_value, but I know that. I decided to ask for an explanation rather than Google or work my way through MSLearn.
The explanation is good. It’s not the current value + 1 it’s the current value + increment, which is a subtlety that some people might miss.
Let’s test this. I wrote some code and then executed it. I get 17, which makes sense. The current value (seen above) is 15 and the increment is 2.
Problem solved. This short session likely will help me remember this detail for future code (or prompts).
SQL New Blogger
This post took me about 8 minutes to setup the code, capture the images, and write this up. I’ve done a lot of these, but I showed how to investigate a question, use AI for help, and then make sure the AI is actually giving me something good.
You could write these types of posts and show your fluency with both data engineering and AI assistance. Take a minute and start writing some blogs that showcase how your skills and career are growing.
Voice of the DBA 
Hey Steve, problem is that the AI assistant was wrong.
You can’t use the current_value column to determine what the next value will be. The design of it was messed up. See my Simple Talk article for details: https://www.red-gate.com/simple-talk/databases/sql-server/how-to-determine-the-last-value-used-by-a-sequence-in-sql-server/ You need to be using the last_value_used column instead.
LikeLike
Thanks, I saw the issue in your article. For new sequences, that’s certainly an issue. I’ll note that in this piece.
LikeLike
Yep, there’s basically no reason to ever use the current_value column. They messed up the design of it, and the replacement column works properly. It’s one of those hard things about maintaining backwards compatibility. Even when you mess up, you get to keep the messed up code indefinitely, in case someone is using it.
LikeLike