{"id":108492,"date":"2026-03-17T14:00:00","date_gmt":"2026-03-17T14:00:00","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=108492"},"modified":"2026-02-20T15:38:25","modified_gmt":"2026-02-20T15:38:25","slug":"how-to-determine-the-last-value-used-by-a-sequence-in-sql-server","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/how-to-determine-the-last-value-used-by-a-sequence-in-sql-server\/","title":{"rendered":"How to determine the last value used by a sequence in SQL Server"},"content":{"rendered":"\n

I’ve been a fan of sequences ever since they were added in SQL Server<\/a> 2012. Prior to that, developers had a choice of IDENTITY<\/code> columns<\/a> or a roll-your-own table mechanism.<\/p>\n\n\n\n

What are sequences in SQL Server?<\/h2>\n\n\n\n

Sequences<\/a> allow us to create a schema-bound object that is not associated with any specific table.<\/p>\n\n\n\n

For example, if I have a Sales.HotelBookings table, a Sales.FlightBookings table, and a Sales.VehicleBookings table, I might want to have a common BookingID used as the key for each table. If more than the BookingID was involved, you could argue that there is a normalization problem with the tables, but we\u2019ll leave that discussion for another day.<\/p>\n\n\n\n

Another reason I like sequences is that they make it much easier to override the auto-generated value, without the need for code like SET IDENTITY_INSERT<\/code> that we need with IDENTITY<\/code> columns. This is particularly powerful if you ever need to do this across linked servers<\/a>, as you\u2019ll quickly find out that it doesn\u2019t work.<\/p>\n\n\n\n

Sequences let me avoid these types of issues: they perform identically to IDENTITY<\/code> columns, and they also give me more control over the cache for available values.<\/p>\n\n\n\n

Exploring an issue not obvious to most people<\/h2>\n\n\n\n

I felt there was a problem with how some of the code associated with sequences was implemented. It worked as documented, but wasn’t useful. Let me show you why. We\u2019ll start by creating a schema and a sequence:<\/p>\n\n\n\n

USE tempdb;\nGO\n\nCREATE SCHEMA Sales AUTHORIZATION dbo;\nGO\n\nCREATE SEQUENCE Sales.BookingID\nAS bigint\nSTART WITH 1\nINCREMENT BY 1\nCACHE 100;\nGO<\/pre><\/div>\n\n\n\n
We could then use this schema as the default value for each of the three tables:<\/p>\n\n\n\n
CREATE TABLE Sales.FlightBookings\n(\n    BookingID bigint NOT NULL\n        CONSTRAINT PK_Sales_FlightBookings PRIMARY KEY\n        CONSTRAINT DF_Sales_FlightBookings_BookingID\n        DEFAULT (NEXT VALUE FOR Sales.BookingID),\n    ...\n);\n\nCREATE TABLE Sales.HotelBookings\n(\n    BookingID bigint NOT NULL\n        CONSTRAINT PK_Sales_HotelBookings PRIMARY KEY\n        CONSTRAINT DF_Sales_HotelBookings_BookingID\n        DEFAULT (NEXT VALUE FOR Sales.BookingID),\n    ...\n);\n\nCREATE TABLE Sales.VehicleBookings\n(\n    BookingID bigint NOT NULL\n        CONSTRAINT PK_Sales_VehicleBookings PRIMARY KEY\n        CONSTRAINT DF_Sales_VehicleBookings_BookingID\n        DEFAULT (NEXT VALUE FOR Sales.BookingID),\n    ...\n);<\/pre><\/div>\n\n\n\n
All this is as expected. However, one question often arises: how do I know the last value for a given sequence? The answer provided by the documentation at the time was to query the sys.sequences<\/code> view. We could do this as follows:<\/p>\n\n\n\n
SELECT s.current_value \nFROM sys.sequences AS s\nWHERE SCHEMA_NAME(s.schema_id) = N'Sales'\nAND OBJECT_NAME(s.object_id) = N'BookingID';\nGO<\/pre><\/div>\n\n\n\n
The current_value<\/code> column in sys.sequences<\/code> is defined as follows:<\/p>\n\n\n\n
Datatype: sql_variant NOT NULL<\/pre><\/div>\n\n\n\n
The use of sql_variant<\/code> here makes sense as the view needs to be able to provide the current value for all sequences, regardless of data type. Sequences can be created with any built-in integer type<\/a>. According to the documentation, the possible values are:<\/p>\n\n\n
\n