Find the first free number in the SQL database

Hello, I have a long table with the fields: PROJECTNUMBER = text field PROJECTNAME = text field PROJECTYEAR = text field the projectnumbers are not strict added. I need an MS-SQL command that ...

LainRobertson

Jan 20, 2024

John180

Hi, John.

This isn't really what SQL Server is built for and it won't scale well, but it is possible.

Example staging script

CREATE TABLE [dbo].[table1]
(
	[number] [int] NOT NULL
	, [name] [varchar](64) NOT NULL
	, [year] [int] NOT NULL
)
GO

INSERT INTO
	[dbo].[table1]
VALUES
	(1, 'Project 1', 2024)
	, (2, 'Project 2', 2024)
	, (3, 'Project 3', 2024)
	, (4, 'Project 4', 2024)
	, (7, 'Project 7', 2024)
	, (8, 'Project 8', 2024)
	, (9, 'Project 9', 2024)
GO

Example query

SELECT
	TOP 1
	[row] AS [newId]
FROM
(
	SELECT
		ROW_NUMBER() OVER
			(
				ORDER BY
					[number]
			) AS [row]
		, [number]
	FROM
		[table1]
) AS [data]
WHERE
	[number] <> [row];

Example output

The more accepted approach is to use an autoincrementing number, which avoids all the overhead of the above method.

Cheers,

Lain