# SQL: Find next unused key value over several tables

I have a MS SQL Database that contains many tables. Three of the tables deal with a temporary numbering scheme. My goal is to write a query that finds the next unused temporary number. If any of the three tables contain a given number, the number is not available for new use at the moment.

This could be restated as: Find the next unused Temporary Order Number (TON). The TON shall be between 99990000 and 999999999 inclusive. If a proposed TON is contained as the value in field OrdNo [defined as char (8)] in ANY of the tables tA, tB, or tC, then the proposed TON is not valid, and a different TON must be used. tA, tB, and tC may or may not contain records with OrdNo in this range. There is no way of knowing how many existing records in the range there might be (could be zero records or thousands of records). Immediately after this code returns a valid TON, other code will insert a new record in table tA having an OrdNo of the computed TON so that subsequent calls will return a new TON. It is acceptable for the code to return no records which would indicate that the low number (99990000) may be used.

I have code working, but when reviewed with "show execution plan", the complex part (the UNION in the view) seems to be run twice, so what I have does not seem efficient.

I would appreciate other ideas of how to attack this.

Thanks,
John

``````CREATE view [dbo].[theUnion] as
SELECT OrdNo
FROM tA_SQL
WHERE (OrdNo >= '99990000')
UNION
SELECT OrdNo
FROM tB_SQL
WHERE (OrdNo >= '99990000')
UNION
SELECT OrdNo
FROM tC_SQL
WHERE (OrdNo >= '99990000')
GO

SELECT TOP 1 OrdNo + 1 AS TON
FROM theUnion
WHERE ((OrdNo + 1) NOT IN
(SELECT OrdNo
FROM theUnion AS theUnion_1))
GO
``````
LVL 1
###### Who is Participating?

Commented:
The basic explanation of the double running of the UNION/view statement is that what you are doing when you create the fview is sort of saving a query, much as in MS Access.  Then, when you use it the next query, you execute it every time you mention it.  (If you mentioned it 5 times, it would execute 5 times)
I am about to propose a solution that I have used successfully in the past but that has been debated (at length) on another forum.
Step 1: Create a small table that has enough columns to hold the ame of the type of number  (NumberType) (whatever you want to have this kind of scheme used for, in this case 'Temporary Order Number') and one (NextAvailableNumber) for the number itself . . . if you want, also create an Identity column.
Step 2: In that table, set the NumberType and the NextAvailableNumber as the Primary Key.
Step 3: And this applies only in your particular starting case, Create a SQL Statement that generates all of the possible combinations of 'Temporary Order Number' and the numbers from 99990000 to 999999999.
Step 4: Now DELETE from this table all of the numbers found using your UNION query.
This leaves you with a table of the unused Temporary Order Numbers that are lower than 1 higher than your highest used Temporary Order Number.
Now, when you need a Temporary Order Number, you check the table for the lowest value in it and, then, you delete that row from the table and use the Temporary Order Number.  When you release the Temporary Order Number for reuse, you simply insert it back into the table.
Now, if there are zero rows in the table when you go to get your next Temporary Order Number, you are going to have to figure out what to do.
Admittedly, this takes up some room in your database; howevver, it is a sure and fast solution to the problem.
P.S You don't "have a MS SQL Database", you have a MS SQL Server database. ;-)
0

Commented:
have you tried uNION ALL instead of UNION?
0

Author Commented:
Changing the two UNIONs to UNION ALLs did not help. The UNION view is still run twice.

Also in looking at the ALL clause decsription, that just allows duplicates to come through. I am not sure how that might help...

Thanks,
John
0

Author Commented:
First off, I understand what you are saying about the double running. It strikes me as strange though since I would have thought the server would see that it was the EXACT same query twice. I would have thought it would save the results of one run in some temporary space for effieciency.

Can you please point me to the previous discussion?

Based on your comment, I did something a little different, but similar...

I created a new table called AllTemp which has 10,000 rows. Each row simply has a possible Temporary Order Number. These are defined as [OrdNo] [char](8) NOT NULL. This field is also the only field in a clustered index. I made a stored procedure to create the table and populate it. Once the table is created, it takes 0.219 MB of space in a database that is 874 MB overall, so in my case the additional size is not a big deal.

So now I am using the original view (using UNION, not UNION ALL). With that I am using a very slightly different query:

SELECT TOP 1 OrdNo + 1 AS TON
FROM AllTemp
WHERE ((OrdNo + 1) NOT IN
(SELECT OrdNo
FROM theUnion AS theUnion_1))
GO

BTW, I discovered that if I defined the AllTemp.OrdNo column as NULL instead of NOT NULL, then there was an additional loop created to discard the null values.

The performance is fine, so I can stick with this as a solution. However I find it hard to believe that such a brute force solution is needed. There must be a more elegant way of doing this...

Many Thanks,
John

0

Commented:
John,
The brute force solution, as you call it, is only necessary if you are doing something that is somewhat off the normal track.  If you have to control the assignment of identifiers (in this case Temporary Orer Numbers), then you also have to create the mechanism to let you have that control.
If you are reusing the Temporary Order Numbers, then you may have some problems down the road with the option you have chosen.  I know that the solution I offered seems a bit odd but it should perform better than the option you chose and only take a minimal amount of additional space.  It also allows for othr temporary numbers but, if Temporary Order Number is all you are "manually" controlling, then using the same table you created but following the procedures I outlined should allow you to select the TOP 1 from that table (if you want the lowest number, set the PK to order the rows DESC; if you want the highest number, then set it to an ASCending sort ;-).  (See SQL_1, below)
In any case, you will need to remove that TON in order to prevent a collision on its use and, if you are reusing them you will need to be able to reinsert them.  I'd create a Stored Procedure to handle the two actions.
A usp_Get_Next_Temporary_Order_Number stored proc could both select the next TON and then remove that one from the table, returning the value to the calling app.  A usp_ReleaseTemporary_Order_Number could use SQL_2 to insert the number.
Also, using the "NOT IN (SELECT . . . ) is a very bad choice for coding SQL.  That is why I used the LEFT OUTER JOIN with atest for one of the JOINed table's columns being NULL.  You may well wind up with your SELECT from the NOT IN clause being executed for every row in the table in order to test the (OrdNo + 1 ) NOT INT.

``````SQL_1:
SELECT TOP 1 TON
FROM AllTemp;

SQL_2:

INSERT INTO AllTemp
VALUES
(
@TON
);
``````
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.