A table in the database for generating primary keys?

Question

Do you ever use a separate table to “generate” artificial primary keys for a database (and why)? I mean to have a table with two columns, the name of the table and the current identifier, with which you can get a new "identifier" for some table by simply locking the row with the name of this table, getting the current key value, increase it one by one and unlock the row . Why do you prefer this over the standard integer identifier column?

PS "Idea" is taken from the Fowlers templates for enterprise application architecture, btw ...

Answer 1

This is called a Hi / Lo assignment.

You would do this with the INSERT trigger in your tables, getting the identifier from this table and incrementing it before or after getting your identifier, depending on your choice.

This is commonly used when you have to deal with several database engines. An auto-incrementing identifier in Oracle is a SEQUENCE that you increment with SEQUENCE.NEXTVALUE due to a BEFORE INSERT TRIGGER in your data table.

In contrast, SQL Server has IDENTITY columns, auto-incrementing initially, and this is managed by the DBE itself.

For your software to work on both DBEs, you must come to some standard, then the most common “standard” used for this is to assign Hi / Lo to the primary key.

This is one approach among others. Nowadays, using ORM Mapping tools such as NHibernate is offered through configuration, so you need to worry less about both the application and the database side.

EDIT NO. 1

Since this kind of maneuver cannot be used for a global area, you will have to have such a table for each database or database schema. Thus, each circuit is independent of the other. However, data in one schema cannot implicitly move to another with the same key, because, perhaps, this would contradict an existing line.

As for the security scheme, it accesses the same database as another scheme or user, so there is no additional table for a specific security scheme.

Answer 2

Whenever you can use SQL Server identifiers or guides, you should. However, there are several situations where this may not be possible.

One example is that the sql server allows only one column for each table. Rarely, a table will have entries that require both a private identifier and a public identifier, and the limit of one identification column means that generating integers can be a pain. You can always use the manual for one, but you want the integer on the private identifier to be fast, and you also want the public identifier to be more understandable to the person than the manual.

In this situation, an additional table for creating identifiers may make sense. However, I would do it differently. There are still two columns in the table, but make one table "shadow" or "Id mapping" for each real table. One of the columns will be your personal identifier (a unique restriction), and one will be your public identifier (the identification may be an increment value of "7" or "13" or another number that is less obvious than "1").

The main difference here is that you do not want to do the lock yourself. Let the SQL server process it.

Answer 3

The only thing I have ever used is when I had an application in BTrieve and it didn't have an identifier column. And I must also say when they tried to use this table, it caused a significant slowdown when they tried to import data due to all the extra reads and records. My friend looked at him and rewritten how they did it to speed it up, but the moral of this story is that if you do something like that wrong, there can be cruel consequences.

Personally, I don’t think I will ever want to do this. Too high probability of error. Two people are trying to use the same key because they forgot to lock the table before capturing the identifier. It just looks like something should be left before the RDBMS, if at all possible. As it will be brought up, it is easy to minimize this situation, but if you do not know what you are doing, it can happen.

Answer 4

You will not prefer this at all.

Whatever you gain by using the template or becoming a DB agnostic, you will lose headaches, support, and performance.

Answer 5

lock the row with the name of this table, get the current key value, increase it by one and open the row

That sounds easy, right?

UPDATE TableOfId SET Id += 1 OUTPUT Inserted.Id WHERE Name = @Name; 

In reality, it is a disaster. There is no activity in the application as a separate operation: all operations are part of transactions. You can’t just “unlock” a row, because “unlocking” actually happens only during commit. This means that all transactions that need an identifier on the table are serialized, and only one can be executed at any time. It also means that a transaction that has access to more than one table is likely to be blocked when updating the identifier table, because forcing the update procedure to “get the next identifier” is difficult in practice.

To avoid full serialization, you need to get identifiers on separate autonomous transactions that can immediately commit transactions (usually an implied transaction with automatic commit on UPDATE itself). But this greatly complicates the application logic. Each operation must support two separate database connections, one for normal transaction logic and one for obtaining the necessary identifiers. Even then, updating identifiers can become such a hot spot that it can still cause visible conflicts and blockages (similar to the terrible "number of views of the update page +1" common in web applications).

In short: use IDENTITY. Identity generation is optimized for high concurrency.

Answer 6

I saw this template used when data created in one database needs to be transferred, backed up, clustered, or put into another database. In this situation, first of all, you want the primary keys not to need to be changed. Secondly, foreign keys. Thirdly, foreign keys or reliable links.