Solved

SQL: Find next unused key value over several tables

Posted on 2009-04-13
6
854 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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Suggested Solutions

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

743 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now