Argthtjtr

In a production system on SQL Server all ids(mostly PKs) in all tables are generated automatically and I am informed that they are unique globally, I mean no 2 ids are the same in the database, even if tables are different. I want to know how this can be done? If there are multiple ways please list them all. Thanks.

Back in the day we had an ID table. Single column, single row with an int value. Every transaction first updated that table to get a new value, which was then used wherever it was needed. This was, of course, a great source of concurrency errors.

Later, sequences were introduced. A single sequence used across the whole database would show the behaviour you describe.

It is just possible the application, not the database, assigns and controls these unique numbers. The code would produce an ID value before the INSERT is submitted to the DB. It would act just like the DB Sequence but be implemented in a different architectural tier.

For unique ID values in the same table, I presume you are aware of the commonly used IDENTITY option, usually using a 32-bit value starting from 1 (so for defining a PK this way something like ID INT NOT NULL IDENTITY(1,1) PRIMARY KEY). You can of course use a larger (BIGINT) if the table might need more than 2,147,483,647 rows.

SQL Server has the option of defining your own sequence which can be shared between multiple tables, potentially all of them. See https://docs.microsoft.com/en-us/sql/t-sql/statements/create-sequence-transact-sql for details. You then define each ID column as ID INTEGER DEFAULT NEXT VALUE FOR The_sequence_You_Defined PRIMARY KEY. There are some things to be aware of here though. Unlike with IDENTITY you are not blocked from dropping in any old value (that isn't already present) as the sequence value is applied by the default only if one is not explicitly given, which could be problematical. Also, using a sequence performs a little more slowly and can become a bottleneck as all tables rely on the same object, though both of these issues are only a concern if your database sees a lot of insert activity in a short space of time. NEXT VALUE FOR The_sequence_You_Defined can be used elsewhere too (i.e. SET @someVariable = NEXT VALUE FOR The_sequence_You_Defined;) which means that if you need IDs to be generated elsewhere in your application logic you can have it done this way (in fact I've seen this used even for a single identity, not just sharing a sequence between multiple objects).

A more hacky approach could be to use a BIGINT for each identity column and start each at a different multiple of (for example) 4,000,000,000. This will work in other DBs and avoids the bottleneck issue, but does double the size of your key and could give you a maintenance nightmare if you accidentally define two tables with IDs starting at the same point. You may wish to add check constraints to make sure an identity value defined this way can't overflow into another value's number space, which adds back in some performance concern.

If you don't mind the larger key, then UUIDs are useful and have the added advantage of being unique between databases (all databases, as the name suggests) not just between tables in one database. As with a sequence these are applied with a default constraint, i.e. ID UNIQUEIDENTIFIER NOT NULL PRIMARY KEY DEFAULT NEWID(). These are 128-bit values though, twice the size of BITINT and four times the size of a "standard" 32-bit INTEGER. If you are concerned about the potential for extra fragmentation caused by the randomness of v4 UUIDs you can use NEWSEQUENTIALID() instead of NEWID() which should still be unique enough (the chance of a collision in the lifetime of this galaxy is vanishingly small).

First of all, I have to mention that I did not work with SQL Server, so I cannot point out some specific features.

I have two concepts of how this can be done on my mind:

1. One sequence to rule them all: This concept is easy as it sounds. You have one sequence which is responsible for generating IDs for every row inserted to any table. In my last job, we were using this concept. Implementation depends on a lot of circumstances, so I will let you decide. But one way is to have some stored procedure which will retrieve the next value of the sequence before any insert.


2. Timestamp: You can somehow incorporate timestamps into your IDs

In the SQL Server world you can refer to this:
NEWID() documentation - newid is compliant with RFC4122