Solved

SQL: Find next unused key value over several tables

Posted on 2009-04-13
6
862 Views
Last Modified: 2012-06-27
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

0
Comment
Question by:exactjb
  • 2
  • 2
6 Comments
 
LVL 60

Expert Comment

by:chapmandew
ID: 24130127
have you tried uNION ALL instead of UNION?
0
 
LVL 1

Author Comment

by:exactjb
ID: 24130205
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
 
LVL 22

Accepted Solution

by:
8080_Diver earned 500 total points
ID: 24130441
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
 
LVL 1

Author Comment

by:exactjb
ID: 24131129
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
 
LVL 22

Expert Comment

by:8080_Diver
ID: 24131430
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

0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question