Sequence generation method of SQL SERVER2005

In addition, the real solution also comes from the management system of reagent consumables. In the past, to ensure uniqueness, a bar code was generated using the date-time + sequence method. To ensure that it is not repeated, the time is fine, in milliseconds. Theoretically, there is still a possibility of repetition.

In fact, repetition is a small probability, but the bar code is too long. For example, the bar code of a reagent is 20140801162430234001. Such a large string cannot be easily identified by the bar code gun, second, even recognition can easily lead to the first half of the recognition. If you do not identify your loss, it will take a lot of time.

The bar code is similar to the primary key of a database. The shorter the bar code, the better.

Since SQL server does not have a sequence generator, we should use tables.

The basic idea is to create a table with auto-incremental ID, time, and serial number, and retrieve the serial number of the table on a daily basis. Add one each time and write it back to the table. The bar code is the date + serial number.

So what should we do if two programs are reading and modifying the table at the same time?

1. Transactions. Because the transaction is the exclusive lock in SQL server, we think that as long as the transaction is enabled in the program, this problem should be solved. The following is the test code:

The code is as follows:

Copy code

Dbquery. Connection. BeginTrans; Dbquery. SQL. clear; Dbquery. SQL. Add ('select * from lrm_bar where datediff (& quot; d & quot;, B _time, getdate () = 0 '); Dbquery. Open; If dbquery. RecordCount = 0 then Begin Dbquery. SQL. clear; Dbquery. SQL. Add ('Insert into lrm_bar (B _time, B _num) values (getdate (), 1 )'); If (dbquery. ExecSQL = 1) then Begin Result: = Self. NewBarHead + GetNumble (1 ); Query. Connection. CommitTrans; End; End Else Begin INum: = dbquery. Int ('B _ num '); StrID: = dbquery. Str ('B _ id '); INum: = iNum + 1; Dbquery. SQL. clear; Dbquery. SQL. add ('update lrm_bar set B _num = '+ ''' + Inttostr (iNum) + ''' + 'Where B _id = '+ ''' + strID + ''''); If (dbquery. ExecSQL = 1) then Begin Result: = Self. NewBarHead + GetNumble (iNum ); Query. Connection. CommitTrans; End; End; Except Dbquery. Connection. RollbackTrans; Result: = ''; End;

Then I started two programs and obtained the sequence at the same time, and found that there were duplicates. Therefore, it is concluded that the transaction lock should be limited to this link.

II. Lock.

Then we can only manually lock the table until I have finished the operation. Change the preceding SQL query statement:

Select * from lrm_bar with (TABLOCKX) where datediff ("d", B _time, getdate () = 0

TABLOCKX is the forced use of an exclusive table lock in SQL server. This lock prevents any other transactions from using this table during the transaction. Until the transaction is committed or rolled back.

Then I will test two programs at the same time to obtain the sequence:

A: 01 03 05 07...
B: 02 04 06 08...

OK will not be repeated.

3. Barcode length.

Solved the problem of sequence generation, and finally discussed the bar code length problem.

Because the number of reagents in the database per day does not exceed one thousand, the serial number must be-. If the date is set to one digit, two digits are used for each month and five digits are used for each day. The generated barcode number is eight bits, which is much shorter than the original one.

40801001. If the scan fails, you can input it by hand.

The generation of the sequence is complete. If you have a better method, you must make a noise.