The issue with changing that is that the model that entity framework populates is expecting an int as the type. This could cause all sorts of unintended fun.
I had to do something similar for our convoluted receipt numbers. I used a combination of a sequence and stored procedure and then a ticket tag - or ticket state if it’s not important for non admin users to see it.
/* Sequence for ticket number generation */
IF OBJECT_ID('dbo.s_TicketNumberGen') IS NOT NULL
DROP SEQUENCE dbo.s_TicketNumberGen;
CREATE SEQUENCE s_TicketNumberGen
START WITH 1
INCREMENT BY 1
/* Generate custom ticket number */
IF OBJECT_ID('dbo.p_GenerateCustomTicketNumber') IS NOT NULL
DROP PROCEDURE dbo.p_GenerateCustomTicketNumber;
CREATE PROCEDURE dbo.p_GenerateCustomTicketNumber
SET NOCOUNT ON;
DECLARE @SeqNr INT = NEXT VALUE FOR s_TicketNumberGen;
DECLARE @YearMonth NVARCHAR(7) = CONVERT(VARCHAR(7), GETDATE(), 126);
SELECT CONCAT(@YearMonth, '-', @SeqNr) AS TicketNumber;
You could use a script to call the stored procedure and return its value to update ticket tag/state or inline it with REPORT SQL DETAILS. The column name returned is
The sequence as it is should guarantee sequential generation. If it’s not that big of a deal, for better performance you can change
NO CACHE to
CACHE x where x is an int greater than 1. The db engine will cache x values and return those instead of calculating the value every time (hitting the db). But if the db engine is restarted, those cached values go away so you could wind up with gaps in the sequence.
If you need to reset the sequence, say on a monthly basis, execute
ALTER SEQUENCE s_TicketNumberGen RESTART;.