Home
Forum: "Bases";
Current archive: 2002.01.08;

Down

#### Determine the first free ID Find similar branches

S_King   (2001-12-04 10:18) [0]

Help wizards how to determine the free 1 number in the stored procedure? We have a table with a filled field (1,2,4,5 ...), i.e. The procedure should issue 3.

Only "crooked" - search through rows, or with the help of an auxiliary table. Maybe you can set the task differently?

Владислав   (2001-12-04 10:57) [2]

Make a selection, sorted in ascending order. Run through all the records (well, not all, but until you find the number you need) find what you need and use.

Only if you want to get a unique number, this is not the best option (and perhaps the worst). Imagine what happens if two users simultaneously receive such a number.

Yuvich   (2001-12-04 16:11) [3]

If the task is exactly like this, then to eliminate the situation described by Vladislav ("Imagine what happens if two users receive such a number at the same time."), You should apply what Vadim suggests ("using an auxiliary table").

Auxiliary table is a list of "holes" in the rooms. How the hole was formed - it was inserted into this table; how to fill the hole - read the minimum number from this table and delete this number from this table. Everything is done in triggers and when the "hole table" is locked, so two users will never read the same number. If the calculus method is used, then you need to block the entire “target table” - which is bad for other users who can simply read the data.

S_King   (2001-12-04 16:43) [4]

All this is understandable, but it may be possible to scroll through the cycle.
from 1 to max (Id) in the store. procedure?

P / S: Sorry, it may be a poorly formulated question.

It is possible, but there is a "but": it is not guaranteed that two users will not simultaneously start "spin the cycle" and will not stumble on the same "hole" (I personally assess this probability as very high). If, by the condition of the problem, it is permissible - in the way, if not, see above.

dmitryK   (2001-12-04 17:12) [6]

If you use a SQL server, then it is logical to solve such problems with a SQL query, i.e. like that

select min (t1.ID) + 1
from tabX t1 left join tabX t2 on t1.ID = t2.ID + 1
where where t2.ID is NULL

it will still work faster than brute force. And you can also implement this with the help of the remote entries log. You hang the trigger to delete the record (befora) and save all the ID of the deleted records in a separate table. In this case, when adding new records, there is virtually no loss of time for searching (critical when simultaneously adding a large number of records). And so that two users would not capture one ID, and you add an entry to the record in one transaction.

S_King   (2001-12-05 09:55) [7]

But how to make a cycle for a single table?

dmitryK   (2001-12-05 10:49) [8]

Something now I do not understand, you asked how to find the minimum free ID. I gave you a query how to do it. Why, one wonders, do you also need to organize a cycle ????

S_King   (2001-12-05 11:21) [9]

I, that did not understand the query text, is it possible to create it for the same table?

dmitryK   (2001-12-05 12:33) [10]

select min (t1.ID) + 1
from tabX t1 left join tabX t2 on t1.ID = t2.ID + 1
where where t2.ID is NULL

tabX - this is your table. She unites herself. As a result, you get a request that includes two columns with the ID
the first is all the IDs in your table,
the second - if ID + 1 is, then its value, if not, then NULL

where where t2.ID is NULL - leaves only those where the second column is NULL, i.e. ID + 1 free.

min (t1.ID) + 1 - returns the minimum free ID number.

Such a request has only one drawback, there must be at least one record in the table, and the free ID number will always be greater than the minimum busy.
But that's another story...

S_King   (2001-12-05 16:32) [11]

I do not know why, but I have this query hangs the database to death.
tried in consol, expert

Pages: 1 whole branch

Forum: "Bases";
Current archive: 2002.01.08;