Scalar function to get last sequence value used, similar to SCOPE_IDENTITY()
While it is currently possible to get the last sequence value(s) used via the OUTPUT clause, that is a bit of extra work -- creating the table / table variable, selecting from it into a variable, etc -- when dealing with a single-row insert.
Upvotes: 1<=-=Nov 17 2016 6:36AM=-=>
Solomon, such function already exist, NEXT VALUE FOR , see https://msdn.microsoft.com/en-CA/library/ff878370.aspx?f=255&MSPPError=-2147217396, and gets value before insert, not after. Knowing it before is a lot more advantageous than after as with Scope_Identity IMHO<=-=Nov 18 2016 10:50AM=-=>
Thank you for reaching out to Microsoft.
The current value of a sequence is exposed by the sys.sequences catalog view (https://technet.microsoft.com/en-us/library/ff877934(v=sql.110).aspx)
Would this work in your scenario?
Hi @Panagiotis. Thanks for the reply. To be honest, I don’t have a current scenario that I need this for. I was just doing some testing with Sequences and thought that it might be nice to have. It would have made what I was doing at the time easier to validate, so I figured others might have run into this as well, especially since SCOPE_IDENTITY() is used so frequently.
No, the “current_value” column in sys.sequences doesn’t do the same thing because it is not constrained by the current scope. It reflects the current value, even when that value has incremented due to concurrent usage in another session. In fact, the value can potentially increase even due to a trigger in the same session that inserts into a table using the same sequence, which ends up being the same problem as with @@IDENTITY, and why we must use SCOPE_IDENTITY() instead. And I did just verify this behavior to be sure, at least across two sessions (not specifically the Trigger issue).
With regards to @Vladimir’s suggestion of using “NEXT VALUE FOR”, yes, that can sometimes work, and in fact I was already using that function in the DEFAULT constraint on the column being used as the Primary Key. It comes down to being an issue of convenience. I would expect that when using Sequences, they will mostly already be using “NEXT VALUE FOR” in a DEFAULT Constraint so that one can insert into a table without always needing to generate the value first. Yes, that does still leave open the possibility of selectively pre-generating the Sequence value, hence not needing a function similar to SCOPE_IDENTITY(), but I was figuring that there might be cases when that won’t work (for whatever reason) and / or people might still appreciate having the function so as not needing to recode too many things if needing this value is an after-thought, or if migrating a large amount of code from using IDENTITY over to using a SEQUENCE and having a lot of code already using SCOPE_IDENTITY().
Given that there is a work-around (and no urgent need) I don’t expect this to be a high-priority, but I figured I would at least put it out there, especially if it was a simple thing to code ;-).<=-=Mar 21 2017 1:10PM=-=>
Thank you for your feedback!
We will consider this feature for the future releases of SQL Server and Azure SQL Database.