Link to home
Start Free TrialLog in
Avatar of exactjb
exactjbFlag for United States of America

asked on

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

Open in new window

Avatar of chapmandew
chapmandew
Flag of United States of America image

have you tried uNION ALL instead of UNION?
Avatar of exactjb

ASKER

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
ASKER CERTIFIED SOLUTION
Avatar of 8080_Diver
8080_Diver
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of exactjb

ASKER

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

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
);

Open in new window