SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 How to select First Missing Number in Order
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mkdlmr
Starting Member

21 Posts

Posted - 02/21/2014 :  16:00:17  Show Profile  Reply with Quote
Hi All,

I am assigning temporary Ids in my table, and right now I am assigning new ones via find the MAX(Temp_Id), stripping out the number and adding one.

Since these are temporary I would like to be able to reuse older ones when they are no longer in the table.

For example I have:
ID0001
ID0002
ID0003
ID0006

My code right now will add ID0007. I would like to re-use ID0004 and ID0005 since they're no longer in use. How do I go about do this?

Thanks,
MK

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 02/21/2014 :  16:13:58  Show Profile  Reply with Quote
Seems like a lot of processing overhead for no real gain.. but, here are two ways to do that:
DECLARE @Foo TABLE (TempID VARCHAR(20))

INSERT @Foo
VALUES
('ID0001'),
('ID0002'),
('ID0003'),
('ID0006')


-- Method 1
;WITH 
Tens (N)     AS (SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL
                 SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9), 
Thousands(N) AS (SELECT t1.N FROM Tens t1 CROSS JOIN Tens t2 CROSS JOIN Tens t3), 
Millions (N) AS (SELECT t1.N FROM Thousands t1 CROSS JOIN Thousands t2), 
Tally (N)    AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) FROM Millions)

SELECT
	MIN(T.N)
FROM
	Tally AS T
LEFT OUTER JOIN
	@Foo AS F
	ON T.N = CAST(STUFF(TempID, 1, 2, '') AS INT)
WHERE
	F.TempID IS NULL


-- Method 2
SELECT
	MIN(CAST(STUFF(TempID, 1, 2, '') AS INT) + 1) AS NewIDNumber
FROM
	(
		SELECT 
			*, 
			LEAD(TempID, 1) OVER (ORDER BY TempID) NextID
		FROM @Foo
	) AS T
WHERE
	 CAST(STUFF(NextID, 1, 2, '') AS INT) - CAST(STUFF(TempID, 1, 2, '') AS INT) > 1
	OR NextID IS NULL
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 02/23/2014 :  06:08:28  Show Profile  Reply with Quote
quote:
Originally posted by mkdlmr

Hi All,

I am assigning temporary Ids in my table, and right now I am assigning new ones via find the MAX(Temp_Id), stripping out the number and adding one.

Since these are temporary I would like to be able to reuse older ones when they are no longer in the table.

For example I have:
ID0001
ID0002
ID0003
ID0006

My code right now will add ID0007. I would like to re-use ID0004 and ID0005 since they're no longer in use. How do I go about do this?

Thanks,
MK



Ideally there's no need to worry about gaps in identity as records will still be present in sequential order and you'll still be able to retrive them in same order using order by. So I would say it would be a kind of overkill to have logic for reusing identity gaps unless you've been enhausted for new id values

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30249 Posts

Posted - 02/24/2014 :  12:32:09  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Duplicate post. See http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=191509


Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000