Are SQL Server sequences guaranteed to always generate unique values even if called simultaneously from multiple connections?

This is a follow-up question to: Are SQL Server sequences thread safe?

I have two separate stored procedures that are calling the same sequence. The stored procedures are launched “in parallel” from an SSIS package. There is no synchronization of any kind between the two stored procedures (other than the fact that I guarantee that they’ll never be updating the same rows, even though they are updating the same table). That being said, there’s no particular reason that the sequence couldn’t be called more or less simultaneously by the two stored procedures. My question is about exactly what would happen in this case.

In the case of the linked question, the OP had several producer applications that were simultaneously inserting into the table and wanted to know whether they could “count” on them being sequential between the processes (i.e. that if producer 2 called the sequence first, its ID would be smaller than producer 3). (This ended up not being the case due to a race condition due to the fact that generating the IDs and storing them were separate steps).

The same logic would presumably apply to my case (that I can’t count on them to be in any particular “order” due to the fact that I also produce and store them in separate steps). In my case, however, I don’t particularly care whether they’re sequential, just that they’re unique.

Can I count on that being the case? Are SQL Server sequences guaranteed to always produce unique values (even if called more or less simultaneously from different connections), or could there be some race condition here that would make this no longer be the case?

Edit: The same sequence number could ultimately be added to multiple rows if that matters (although it will always be added to at least one). I fetch the number from the sequence and then do an update query to add it to the rows that I want it to be part of.

Answer

If I read correctly, you are just ensuring they are unique (example: you want them to be a primary key?). If so, that is correct. As far as guaranteed order, you are correct that there are conditions, esp. under load, they will not be in a particular order. Does not sound like that is a big problem for you. As long as you are pulling the next value correctly, you are safe.

When I look at created sequences, I think of them like autonumbering in Oracle, where you have to pull the value and then utilize it, rather than IDENTITY in SQL Server (although there are ways to increment IDENTITY to “fill in the hole” later, so it can be utilized in the same/similar manner).

I have not examine the internals, but I would imagine the base sequence concept is utilized for IDENTITY underneath the hood, as the ideas are essentially the same, except IDENTITY is attached to a field in the table.