SQL: Hand out Unique Numeric IDs by a given prefix
-
So, I have a set of requirements:
- Given a certain set of prefix characters generate a sequence of numeric Ids such that:
- Numbers are generated in ascending integer order
- Every number is generated once and only once for a given prefix, regardless of whether the number is later used or not
- Each prefix starts at ID
1
, proceeds to2
, then3
, and so on. - It is impossible to generate the same Id for a given prefix twice
- The solution must not summon elder gods into my codebase.
Given these requirements...... on a scale of "not bad actually" to "OMFG?! WHAT WERE YOU THINKING YOU STUPID
BITCHVIXEN?!" How is this solution and can it be improved?CREATE TABLE [PrefixedIds] ( RecordId INTEGER IDENTITY (1,1) PRIMARY KEY, Prefix NVARCHAR(10) NOT NULL, CurrentId INTEGER DEFAULT(0) NOT NULL ); GO CREATE PROCEDURE [AssignId] ( @forPrefix NVARCHAR(10) ) AS BEGIN TRANSACTION -- Obtain a table lock to prevent accidently handing out the same ID twice for the same prefix DECLARE @locker INT; SELECT TOP 1 @locker = RecordId FROM [PrefixedIds] WITH (HOLDLOCK, TABLOCK); DECLARE @id INT; IF EXISTS (SELECT 1 FROM [PrefixedIds] WHERE Prefix = @forPrefix) BEGIN SELECT @id = CurrentId + 1 FROM [PrefixedIds] WHERE Prefix = @forPrefix; UPDATE [PrefixedIds] SET CurrentId = @id WHERE Prefix = @forPrefix; END ELSE BEGIN SET @id = 1; INSERT INTO [PrefixedIds](Prefix, CurrentId) VALUES (@forPrefix, @id); END SELECT @id AS AssignedId; COMMIT GO
- Given a certain set of prefix characters generate a sequence of numeric Ids such that:
-
@accalia I'm going to assume MSSQL doesn't support sequences?
-
SQL Server 2012 or later? Then come get some:
I've never used it myself, but it appears to cure what ails ya.
-
@Onyx it does..... do they solve my problem?
Did i mention that the provided prefix is from an external soucre and completely outside my control, they can and have changed in production without warning......
-
So I could have WEB1 and XXX1, because the prefix is different?
Seems about right. You're looking at what Navision would call a number sequence.
Table:
Prefix (pk)
Start number
End number
Current number (reference)And there's a function that says "Get next number sequence (prefix)"
Which effectively does what you have:
BEGIN TRANSACTION if a valid number series prefix was passed in if current number is blank number = start number elseif current number > end number throw an "oh shit" exceltion else number = current number end if return value = prefix + number update table set current number = current number +1 end if COMMIT TRANSACTION
-
@accalia said in SQL: Hand out Unique Numeric IDs by a given prefix:
The solution must not summon elder gods into my codebase.
Because we don't have a :crying_cthulhu_face: emoji…
-
@accalia said in SQL: Hand out Unique Numeric IDs by a given prefix:
the provided prefix is from an external soucre and completely outside my control
Hmm... I guess you could try to create a new sequence on the fly for a prefix that you've never encountered before... although that now makes for a kludge...
-
@lolwhat Hmmm... only supports integer types... still, that's tracked internally anyway, yes?
Well, since you can't generate the entire thing as a sequence... how many prefixes do you expect? If you have a table of prefixes you could potentially create a sequence for each one, would get rid of the table lock dance...
NB: I'm assuming that it's feasible to do something like
IF NOT EXIST CREATE SEQUENCE ... ELSE seqenceBlah.nextval();
Edit: man, so many accalias in that pseudo-SQL snippet... Appropriate, shipit!
-
@Onyx said in SQL: Hand out Unique Numeric IDs by a given prefix:
. how many prefixes do you expect?
we've got about 200 in the current system.
16 or so seem to be active right now
-
@Onyx Again, I'm a n00b to T-SQL sequences myself, but yeah, I'm willing to bet they're tracked internally somehow. You can easily SELECT @Prefix + CAST(<next sequence ID> AS VARCHAR(whatever)) anyway, to get it into string form.
@accalia Assuming you're not gonna have a kajillion prefixes, it seems feasible to create new ones dynamically. The sticky bit is checking for existence of a sequence for a given prefix, then creating the sequence if it doesn't exist, in an atomic fashion. I foresee some spearmintin' ahead.
Then again, if you don't really care about needing e.g. FOO1 and BAR1, you could get away with having exactly one sequence...
-
@lolwhat said in SQL: Hand out Unique Numeric IDs by a given prefix:
spearmintin
but i only have wintermint gum..... is that close enough?
-
@lolwhat said in SQL: Hand out Unique Numeric IDs by a given prefix:
in an atomic fashion
Yeah, that's why I thought of sequences at first, they should be guaranteed atomic but...
Actually, since prefixes shouldn't be something that changes very often, maybe create new ones using a trigger as needed? Should fix up the atomic problem, hopefully?
@lolwhat said in SQL: Hand out Unique Numeric IDs by a given prefix:
Then again, if you don't really care about needing e.g. FOO1 and BAR1, you could get away with having exactly one sequence...
Also, this.
-
@Onyx said in SQL: Hand out Unique Numeric IDs by a given prefix:
maybe create new ones using a trigger as needed?
thought about that one..... unfortunately the way i know a new prefix has been generated is when it hits my system looking for an ID from an externally hosted cloud service thingummy
-
@accalia said in SQL: Hand out Unique Numeric IDs by a given prefix:
@lolwhat said in SQL: Hand out Unique Numeric IDs by a given prefix:
spearmintin
but i only have wintermint gum..... is that close enough?
Certainly, dear. Just stay away from badmint. Don't get cocky.
-
@accalia said in SQL: Hand out Unique Numeric IDs by a given prefix:
unfortunately the way i know a new prefix has been generated is when it hits my system looking for an ID from an externally hosted cloud service thingummy
Oooh, nasty...
Still, the prefix table is maintained by you, no? When do you add those? Or did I misunderstand and there's no such table?
-
@lolwhat said in SQL: Hand out Unique Numeric IDs by a given prefix:
Then again, if you don't really care about needing e.g. FOO1 and BAR1, you could get away with having exactly one sequence...
i wish.... i tried to convince the business they only needed one sequence..... i actually saw lit torches and sharpened pitchforks at that meeting.
@Onyx said in SQL: Hand out Unique Numeric IDs by a given prefix:
Still, the prefix table is maintained by you, no? When do you add those? Or did I misunderstand and there's no such table?
there is no prefix table.... the one mentioned above is merely my solution for tracking the buggers. some vendor's system chooses them and then changes them whenever it wants to with no rhyme or reason i can see.......
-
@accalia said in SQL: Hand out Unique Numeric IDs by a given prefix:
i tried to convince the business they only needed one sequence..... i actually saw lit torches and sharpened pitchforks at that meeting
Did they actually have a good reason for bitching about that? Otherwise, you'll be hacking something together to get around the problems with creating a brand-new sequence dynamically. Now that you know about CREATE SEQUENCE, I'd say you should push back on them - after all, it's the Agile Way.
-
@accalia said in SQL: Hand out Unique Numeric IDs by a given prefix:
some vendor's system chooses them and then changes them whenever it wants to with no rhyme or reason i can see.......
Fuck 'em!
Ehm, I mean... What do you use the things for, then? I mean, if you use them for logging requests you have to run a
BEFORE INSERT
trigger every time regardless, yes? In which case, it should still be atomic since that will lock the table anyway... Or are we talking something async and/or weird?
-
@lolwhat said in SQL: Hand out Unique Numeric IDs by a given prefix:
Did they actually have a good reason for bitching about that?
apparently, given some of these IDs are used for Invoice IDs this has something to do with VAT authorities.....
-
@accalia In which case a single sequence actually makes more sense to me, personally...
Then again, I'm no beancounter...
-
@Onyx said in SQL: Hand out Unique Numeric IDs by a given prefix:
@accalia In which case a single sequence actually makes more sense to me, personally...
yeah.... but then this is guvm't we're talking about.
-
@accalia I think an UPSERT is what you want (called MERGE in Sql Server): update when found, insert when new. See MERGE on msdn. Or maybe not. It's a construct I've wanted to use for ages now, but somehow for my usecases it just doesn't fit, YMMV...
-
@accalia said in SQL: Hand out Unique Numeric IDs by a given prefix:
So, I have a set of requirements:
- Given a certain set of prefix characters generate a sequence of numeric Ids such that:
- Numbers are generated in ascending integer order
- Every number is generated once and only once for a given prefix, regardless of whether the number is later used or not
- Each prefix starts at ID
1
, proceeds to2
, then3
, and so on. - It is impossible to generate the same Id for a given prefix twice
- The solution must not summon elder gods into my codebase.
Given these requirements...... on a scale of "not bad actually" to "OMFG?! WHAT WERE YOU THINKING YOU STUPID
BITCHVIXEN?!" How is this solution and can it be improved?CREATE TABLE [PrefixedIds] ( RecordId INTEGER IDENTITY (1,1) PRIMARY KEY, Prefix NVARCHAR(10) NOT NULL, CurrentId INTEGER DEFAULT(0) NOT NULL ); GO CREATE PROCEDURE [AssignId] ( @forPrefix NVARCHAR(10) ) AS BEGIN TRANSACTION -- Obtain a table lock to prevent accidently handing out the same ID twice for the same prefix DECLARE @locker INT; SELECT TOP 1 @locker = RecordId FROM [PrefixedIds] WITH (HOLDLOCK, TABLOCK); DECLARE @id INT; IF EXISTS (SELECT 1 FROM [PrefixedIds] WHERE Prefix = @forPrefix) BEGIN SELECT @id = CurrentId + 1 FROM [PrefixedIds] WHERE Prefix = @forPrefix; UPDATE [PrefixedIds] SET CurrentId = @id WHERE Prefix = @forPrefix; END ELSE BEGIN SET @id = 1; INSERT INTO [PrefixedIds](Prefix, CurrentId) VALUES (@forPrefix, @id); END SELECT @id AS AssignedId; COMMIT GO
Sounds like what JIRA does TBH. Wonder how they're doing it?
- Given a certain set of prefix characters generate a sequence of numeric Ids such that:
-
@accalia said in SQL: Hand out Unique Numeric IDs by a given prefix:
apparently, given some of these IDs are used for Invoice IDs this has something to do with VAT authorities.....
There's probably one beancounter somewhere (not necessarily in the government) who really can't cope with the thought of a non-contiguous sequence. All this shit is just deal with that one ic twerp. It's even possible that the real cause of all this has now retired, but the bad behaviour is now baked into the bureaucratic processes of the organisation.
SNAFU, IOW.
-
@Tsaukpaetra said in SQL: Hand out Unique Numeric IDs by a given prefix:
Sounds like what JIRA does TBH. Wonder how they're doing it?
They might be just doing that on the client side, with appropriate locking. JIRA's pretty heavyweight…
[EDIT]: Holy particularly-useless onebox, Batman!
-
@Tsaukpaetra said in SQL: Hand out Unique Numeric IDs by a given prefix:
Sounds like what JIRA does TBH. Wonder how they're doing it?
If I had to guess, I'd say they're
@accalia Yes, the correct answer is to use a sequence for each prefix. That was the first thing I thought of immediately. That pushes the concurrency issue to creating the sequences on the fly, which is also a , but unless you can get some kind of "hey, here's a new prefix" message/instruction from the vendor, you're probably stuck with it. That shouldn't be a big problem, though, since you'll just get a "sequence already exists" error if you try to create it twice. Assuming MSSQL implements sequences correctly, the main purpose of a sequence is that getting the next value is guaranteed to be atomic.
-
@Lorne-Kates said in SQL: Hand out Unique Numeric IDs by a given prefix:
throw an "oh shit" exceltion
Why do you have a spreadsheet in your database?
-
I still like @Onyx 's sequences idea the best.
This seems to work, although I cant get it to return string for some reason...
CREATE SCHEMA Seqs; GO DROP PROCEDURE [GetId]; GO CREATE PROCEDURE [GetId] ( @prefix NVARCHAR(20) ) AS IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'[Seqs].[' + @prefix + ']') AND type = 'SO') EXEC('CREATE SEQUENCE Seqs.' + @prefix + ' START WITH 1 INCREMENT BY 1;'); DECLARE @id INT; DECLARE @sql NVARCHAR(1000); SET @sql = 'SELECT NEXT VALUE FOR Seqs.' + @prefix + ';'; EXEC sp_executesql @sql, N'@id INT out', @id out; DECLARE @result NVARCHAR(20); SET @result = @prefix + CONVERT(NVARCHAR(20), @id); SELECT @result AS result; GO
-
@cartman82 Is the IF NOT EXISTS ... CREATE SEQUENCE in your code guaranteed to be atomic?
-
@lolwhat The whole call of the procedure would be the expected boundary of a transaction by default, yes?
-
@lolwhat said in SQL: Hand out Unique Numeric IDs by a given prefix:
@cartman82 Is the IF NOT EXISTS ... CREATE SEQUENCE in your code guaranteed to be atomic?
I guess not, but the worst that can happen is you error out, so you just try again.
-
@Tsaukpaetra said in SQL: Hand out Unique Numeric IDs by a given prefix:
Sounds like what JIRA does TBH. Wonder how they're doing it?
I have once heard that our corporate JIRA sometimes suffers index corruption, what is actually broken int hat case, and how they handle it. All of that is , and smells like it's running on mongo or something.
-
@PleegWat said in SQL: Hand out Unique Numeric IDs by a given prefix:
our corporate JIRA sometimes suffers index corruption
So does ours. It's not the most resilient software.
-
Status: Enjoying my somewhat popularity...
-
In Postgres, I would do:
create table prefix_sequence ( prefix varchar(10) not null primary key, id bigint not null ); create function next_prefix_id(p varchar(10)) returns bigint as $$ insert into prefix_sequence (prefix, id) values (p, 1) on conflict do update set id = id + 1 returning id $$ language sql;
-
@cartman82 said in SQL: Hand out Unique Numeric IDs by a given prefix:
I still like @Onyx 's sequences idea the best.
Me too.
This seems to work, although I cant get it to return string for some reason...
You need an OUTPUT parameter for that. The implicit return value of a sproc is always an int.
You could wrap the CREATE SEQUENCE statement in a BEGIN TRY END TRY, so you won't have to try again if it turns out to be not atomic. Or set the isolation level to SERIALIZABLE.
Be aware that you could still have holes in your number sequences, because SQL Server generates n numbers every time and might forget about them if the server reboots, so it would start over with the next n numbers.
-
@PleegWat said in SQL: Hand out Unique Numeric IDs by a given prefix:
running on mongo or something.
I doubt it, that's not a supported DB IIRC
-
@PleegWat said in SQL: Hand out Unique Numeric IDs by a given prefix:
like it's running on mongo or something.
Oh yes. MongoDB and sequences. I'd love to see how that turns out. Best case, it silently renumbers your tickets.
-
@ChrisH said in SQL: Hand out Unique Numeric IDs by a given prefix:
Be aware that you could still have holes in your number sequences, because SQL Server generates n numbers every time and might forget about them if the server reboots, so it would start over with the next n numbers.
ah. well then there's sequences out the window. the VAT authorities require that we account for each and every invoice number. if we miss even a single number they are down our throats like a spiky purple dildo at a deep throat competition.
-
@accalia said in SQL: Hand out Unique Numeric IDs by a given prefix:
ah. well then there's sequences out the window. the VAT authorities require that we account for each and every invoice number. if we miss even a single number they are down our throats like a spiky purple dildo at a deep throat competition.
Huh. Seems like they fixed that, or I overlooked it the first time.
You can tell the DB to always get every single value from the table (CREATE SEQUENCE ... NO CACHE
) instead of creating the next n numbers and storing them in memory. It's a bit slower obviously, but you wouldn't have gaps.
-
@cartman82 said in SQL: Hand out Unique Numeric IDs by a given prefix:
EXEC('CREATE SEQUENCE Seqs.' + @prefix + ' START WITH 1 INCREMENT BY 1;');
GetId('A; delete from users; --')
Almost fits in the character limit.
-
@Maciejasjmj the dangers of meta-programming...
-
@ChrisH said in SQL: Hand out Unique Numeric IDs by a given prefix:
Huh. Seems like they fixed that, or I overlooked it the first time.
You should still be careful and test it well, I'm relatively sure Postgres will generate a new number every time you ask it to, and if an
INSERT
fails it won't roll back. I think. Not sure if it does that if you put it in a transaction.Yes, I know we're talking MSSQL here, but the same caveat might apply.
-
@Onyx I'm pretty sure it does. Oracle does this, too. Once you get a value from the sequence, it's burned. Regardless what happens to the rest of your transaction afterwards.
-
@Maciejasjmj said in SQL: Hand out Unique Numeric IDs by a given prefix:
@cartman82 said in SQL: Hand out Unique Numeric IDs by a given prefix:
EXEC('CREATE SEQUENCE Seqs.' + @prefix + ' START WITH 1 INCREMENT BY 1;');
GetId('A; delete from users; --')
Almost fits in the character limit.
yeah. i'm not using EXEC..... the database manager will kill me if i do.
literally kill me.
he even measured me for lead shoes.
-
@accalia said in SQL: Hand out Unique Numeric IDs by a given prefix:
Every number is generated once and only once for a given prefix, regardless of whether the number is later used or not
@accalia said in SQL: Hand out Unique Numeric IDs by a given prefix:
the VAT authorities require that we account for each and every invoice number.
E_MUTUALLY_EXCLUSIVE
-
@JazzyJosh said in SQL: Hand out Unique Numeric IDs by a given prefix:
@accalia said in SQL: Hand out Unique Numeric IDs by a given prefix:
Every number is generated once and only once for a given prefix, regardless of whether the number is later used or not
@accalia said in SQL: Hand out Unique Numeric IDs by a given prefix:
the VAT authorities require that we account for each and every invoice number.
E_MUTUALLY_EXCLUSIVE
tell me about it. but those are the requirements i have....
-
@Maciejasjmj said in SQL: Hand out Unique Numeric IDs by a given prefix:
Almost fits in the character limit.
That's why God invented HEX encoding.
-
@Lorne-Kates said in SQL: Hand out Unique Numeric IDs by a given prefix:
@Maciejasjmj said in SQL: Hand out Unique Numeric IDs by a given prefix:
Almost fits in the character limit.
That's why God invented HEX encoding.
Actually i'm using BASE36 for encoding IDs that aren't needed for VAT reporting and get pretty damn big
-
@Onyx said in SQL: Hand out Unique Numeric IDs by a given prefix:
@ChrisH said in SQL: Hand out Unique Numeric IDs by a given prefix:
Huh. Seems like they fixed that, or I overlooked it the first time.
You should still be careful and test it well, I'm relatively sure Postgres will generate a new number every time you ask it to, and if an
INSERT
fails it won't roll back. I think. Not sure if it does that if you put it in a transaction.Yes, I know we're talking MSSQL here, but the same caveat might apply.
If the sequences absolutely had to have no gaps, I wouldn't really trust anything other than an exclusive lock.
Eg. What happens if there are two concurrent transactions trying to use one sequence, and the first one fails but the second didn't?
Down this path is madness, and is why Oracle and Postgres (and probably others) just go with the simple, burn-a-value approach.