Query to get first number
-
We have a script to get the first available ID number in a table. We are looking for a faster way to write this.
Here is the script.
Select top 1 ISNULL((Select count(BudgetID) from tblBudgetYear where BudgetID <= Bud.BudgetID), MAX(BudgetID)+1)
from tblBudgetYear Bud
where (Select count(BudgetID) from tblBudgetYear where BudgetID <= Bud.BudgetID)
not in (Select BudgetID from tblBudgetYear)
group by bud.budgetID
Table tblbudgetyears
One field budgetid of type int.
Sample data:
3,4,5,7
Query would return 1
Sample Data2:
1,3,4,5,7
Query would return 2
Sample Data3:
1,2,3,4,5
Query would return 6
-
CREATE PROCEDURE GetFirstAvailableBudgetID
AS
SET NOCOUNT ON
--
DECLARE @Temp TABLE ([TempID] INT IDENTITY(1,1))
DECLARE @MaxCurrentBudget INT, @i INT
-- Grab the highest current budget
SELECT @MaxCurrentBudget = MAX(BudgetID) FROM tblBudgetYear
-- Populate Lookup
SET @i = 0
WHILE @i < @MaxCurrentBudget BEGIN
INSERT INTO @Temp DEFAULT VALUES
SET @i = @i + 1
END
--
SELECT TOP 1 tt.TempID
FROM @Temp tt
LEFT JOIN tblBudgetYear b
ON tt.TempID = b.BudgetID
WHERE b.BudgetID IS NULL
--
SET NOCOUNT OFF
GOHTH,
Jay
-
Thanks for the reply. It was a little slower than the one we had. We have found one a little faster than the first post.
Select top 1 B.CID from
(Select (Select count(BudgetID) from tblBudgetYear where BudgetID <= Bud.BudgetID) CID from tblBudgetYear bud) B
where B.CID not in (Select BudgetID from tblBudgetYear)
-
Try it with NOT EXISTS instead of NOT IN:
...
SELECT MIN(tt.TempID)
FROM @Temp tt
WHERE NOT EXISTS (SELECT * FROM tblBudgetYear)... -
use IDENTITY. That's what it's made for.
Or keep your own parameter-file which you access using a genereate-new-id stored procedure.
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Try this...
SELECT ISNULL(min(BudgetId),
ISNULL((SELECT max(BudgetId) + 2
FROM tblBudgetYear), 2) - 1
FROM tblBudgetYear t1
WHERE NOT EXISTS
(SELECT 1 FROM tblBudgetYear t2
WHERE t2.BudgetId = t1.BudgetId - 1)
AND t1.BudgetId > 1 -
By the way alzdba, IDENTITY does not reuse values that have been deleted...
-
How About this
declare @j-2 int
declare @i int
set @j-2=1
declare a cursor for
select [column] from
order by [column]
open a
fetch next from a into @i
while @@fetch_status=0
begin
if @i<>@j
begin
break
end
set @j-2=@j+1
fetch next from a into @i
end
print @j-2
close a
deallocate a
Pay Respect to People on your way up. For you will meet the same People on your way down.
He who knows others is learned but the wise one is one who knows himself. -
Hi reeder17,
quote:
We have a script to get the first available ID number in a table. We are looking for a faster way to write this.
sorry for hooking right in
Do you need this sequential numbering being stored with your table?
Or do you need it only for presentational stuff?
If you need it only for presentational stuff what about doing something like this
SELECT
t2.BudgetID, <other_fields>,
(SELECT COUNT(*) FROM <your_table> t1 WHERE t1.BudgetID <= t2.budgetID) AS MyID
FROM
<your_table> t2
ORDER BY
3Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Thanks for all the posts! We are using this instead of an identity column. We have been able to tweak our query; here is the fastest one that we have ran, we tested with all the posts too.
Select top 1 ISNULL(B.CID,ISNULL(MAX(b.budgetid),0)+1) from
(Select budgetid, 'cid'=(Select count(BudgetID)
from tblBudgetYear where BudgetID <= Bud.BudgetID) from tblBudgetYear bud) B
group by B.CID, B.BudgetID
HAVING B.CID <> B.BudgetID
We probably could use an identity column instead of this way, just not sure what happens when it reaches the max size of size int.
-
When the identity reaches the maximum for an int. It will stop accepting records (overflow error).
The only way around this is using a GUID column, but that adds some overhead (and storage too).
-
quote:
...just not sure what happens when it reaches the max size of size int...
Gee, that's a lot of budgets. You, know, the IDENTITY field was designed for this. It would be a LOT faster, easier, and simpler to use the tools that were designed for the job instead of re-invent the wheel. The max size limit of INT is over 2 trillion, so I don't think you're going to be coming anywhere near that, and if you do, your solution of finding available IDs would be so slow that you'd scrap the idea long before the ID value reached the millions...
-
OK Identity does not reuse. Should it ? Depends on the requirements. If your id column reaches the maximum value (int/bigint !!), you're in big troubles anyway and probably you'll have to adjust you id-producing algorithm. With Identity you could checkident and go on. Besides it one should never deduct anything from an id (low id is older than high id) so who cares about gaps. If you use GUID, you cannot deduct anything, but does your datamodel need that overhead ? There's a lot of info in the net regarding identity and surogate-keys.
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
How about this:
DECLARE @NewBudgetID INT
IF (Select Min(BudgetID) as NewBudgetID from tblBudgetYear) > 1
Set @NewBudgetID = 1
ELSE
Select @NewBudgetID = top 1 BudgetID + 1 from tblBudgetYear x
where not exists(Select NULL from tblBudgetYear where BudgetID = x.BudgetID + 1)
order by BudgetID
On the occaisions where the value 1 is available, the longer query doesn't execute.
-
quote:
Gee, that's a lot of budgets. You, know, the IDENTITY field was designed for this. It would be a LOT faster, easier, and simpler to use the tools that were designed for the job instead of re-invent the wheel. The max size limit of INT is over 2 trillion, so I don't think you're going to be coming anywhere near that, and if you do, your solution of finding available IDs would be so slow that you'd scrap the idea long before the ID value reached the millions...
well said!
I still don't think it's necessary to keep ongoing numbering stored in the db. I assume in addition to BudgetID there is also stored some client identification ID. So the numbering does not apply to this client but only to the table as a whole.
Anyway, just another thought. What about a delete trigger and a lookup table?
Every time a DELETE operation occurs, BudgetID is stored in the lookup table. Next time one needs to fill the holes, he just needs to query the MIN from this table. Not sure what happens when the INSERT transaction fails and how to catch this.
Just another monday morning, one cup of coffee thought
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply