Unique yet simple database identifiers in SQL Server

Question

Firstly, I know this question , and the sentence (using the GUID) is not applicable in my situation.

I want simple UIDs so that my users can easily transfer this information over the phone:

Hi, I have a problem with order 1,584

Unlike

hi, I have a problem with ordering 4daz33-d4gerz384867-8234878-14

I want them to be unique (wide database), because I have several different “objects” ... there are order identifiers, delivery identifiers and billing identifiers, and since there is no one-to-one relationship between them, I cannot guess which object the identifier belongs to.

Thanks to the unique identifiers of the entire database, I can immediately tell which object my client belongs to. My user can simply enter the identifier in the search tool, and I save it for an additional click to clarify what I'm looking for.

My current idea is to use identifier columns with different seeds 1, 2, 3, etc. and the increment value is 100.

This raises several questions:

• What if in the end I get more than 100 types of objects? that I could use 1000 or 10000, but something that does not scale well "smells"

• Is it possible that the seed is “lost” (during replication, database problems, etc.?)

• In general, are there other issues that I should be aware of?

• Is it possible to use a non-integer (I am currently using bigints) as identifier columns so that I can prefix the identifier with something representing the type of the object? (e.g. varchar column)

• it would be a good idea for the user to have a “main table” containing only an identifier column, and possibly an object type, so that I can just insert a row into it when a new idea is needed. I feel this might be a bit too much, and I'm afraid it will get all my insertion requests out of the way. Plus, the fact that I can not determine the type of object without looking at the database

• Are there any other smart ways to solve my problem?

Answer 1

Why not use identifiers in all tables, but at any time when you present them to the user, just click on one char for the type? for example O1234 - order, D123213 - delivery, etc.? This way you don't have to design some kind of crazy scheme ...

Answer 2

Refer to it in the user interface - add the letter (or letters) of the prefix to the identification number, informing the users about it. So o472 would be order, b531 is an account, etc. It’s convenient for people to mix letters and numbers when they give “numbers” over the phone and more precisely than with direct numbers.

Answer 3

You can use the auto-increment column to create a unique identifier. Then, the computed column that takes the value of this column and adds it with a fixed identifier that reflects the type of object, such as OR1542 and DL1542, will be order # 1542 and delivery # 1542 respectively. Your prefix can be expanded as much as you want, and the format can be organized to help distinguish between elements with the same auto-increment value, such as OR011542 and DL021542, with the prefixes being OR01 and DL02.

Answer 4

I would do the definition of a common root table. For lack of a better name, call it Entity. Entity table must have at least one Identity column. You can also include other fields that are common to all of your objects, or even metadata that tells you that this line is an order, for example.

Each of your actual table Order, Delivery ... will have an FK link back to the Entity table. This will give you a unique identifier column

Using seeds in my opinion is a bad idea, and this can lead to problems.

Edit

Some of the problems that you have already mentioned. I also see that it is a pain to track and ensure that all new objects are properly configured. Imagine that the developer updated the system in two years.

After I wrote this answer, I thought, but more about why you are doing this, and I came to the same conclusion as Matt.

Answer 5

The MS intentional programming project had a GUID-to-word system that provided declared names from an arbitrary identifier

Answer 6

Why not just a basic bigint view? http://en.wikipedia.org/wiki/Base_36

Answer 7

We faced a similar problem in the project. We solved this by first creating a simple table that has only one row: BIGINT is set as the auto-increment identifier. And we created sproc that inserts a new row into this table using the default values ​​and inside the transaction. It then saves SCOPE_IDENTITY in a variable, discards the transaction, and then returns the saved SCOPE_IDENTITY .

This gives us a unique identifier inside the database without populating the table.

If you want to know which object the identifier belongs to, I would lose the transaction rollback and save the object type along with the identifier. Thus, find out which object the id belongs to - this is only one choice (or internal connection).

Answer 8

I use a high / low algorithm for this. I can not find a description of this online, though. A must blog about this.

In my database, I have an identifier table with a counter field. This is the high part. In my application, I have a counter that goes from 0 to 99. This is the low part. The generated key is 100 * high + low.

To get the key, I do the following

 initially high = -1 initially low = 0 method GetNewKey() begin if high = -1 then high = GetNewHighFromDatabase newkey = 100 * high + low. Inc low If low = 100 then low = 0 high = -1 return newKey end 

Real code is more complicated with locks, etc., but that’s the general meaning.

There are several ways to get a high value from a database, including auto-increment keys, generators, etc. The best way depends on the dB you use.

This algorithm produces simple keys, while avoiding most of the db hits every time it scans a new key. In testing, I found that it has similar performance with pipelines and significantly higher performance than every time using the auto inc key.

Answer 9

You can create a master UniqueObject table with your personality and subtype. Subtitles (Orders, Users, etc.) Will have FK for UniqueObject. INSTEAD OF INSERT triggers should keep the pain to a minimum.

Answer 10

Maybe the option itemType-year-week-OrderNumberThisWeek?

 o2009-22-93402 

Such an identifier may consist of several database column values ​​and is simply formatted in the form of an identifier by the software.

Answer 11

I had a similar situation with the project.

My solution: by default, users see only the first 7 characters of the GUID.

It is random enough that collisions are extremely unlikely (1 in 268 million), and it is effective for talking and typing.

Inside, of course, I use the whole GUID.