To find the next available ID

,

There are times when you have a limited range of ids and these have to be re-used as they become available.

The script lists the first missing id in a table.

Note that this script works best for a table with a limited number of rows, say sub 100,000

I tested this on a PIII 500MHz 256Mb RAM on a 1 million record table with 500 records missing and only 1 user and it took 14 seconds.

SELECT MIN(DT.Missing)

FROM	Tbl_Test AS A RIGHT JOIN
	(	SELECT Id+1 AS Missing FROM Tbl_Test) AS DT
	ON A.Id = DT.Missing
WHERE	A.Id IS NULL

Rate

Share

Share

Rate