Encontre o menor número não utilizado no SQL Server

Como você encontra o menor número não utilizado em uma coluna do SQL Server?

Estou prestes a importar um grande número de registros gravados manualmente do Excel para uma tabela do SQL Server. Todos eles têm um ID numérico (chamado número do documento), mas eles não foram atribuídos sequencialmente por motivos que não se aplicam mais, ou seja, a partir de agora, quando meu site registra um novo registro, ele precisa atribuir o menor número de documento possível ( maior que zero) que ainda não foi usado.

Existe uma maneira de fazer isso através de SQL simples ou isso é um problema para TSQL / code?

Obrigado!

EDITAR

Agradecimentos especiais à WW por levantar a questão da concorrência. Dado que este é um aplicativo da web, ele é multi-threaded por definição e qualquer um que se deparar com esse mesmo problema deve considerar um bloqueio de código ou de nível de database para evitar um conflito.

LINQ

FYI – isso pode ser feito via LINQ com o seguinte código:

var nums = new [] { 1,2,3,4,6,7,9,10}; int nextNewNum = ( from n in nums where !nums.Select(nu => nu).Contains(n + 1) orderby n select n + 1 ).First(); 

nextNewNum == 5

Encontre a primeira linha onde não existe uma linha com Id + 1

 SELECT TOP 1 t1.Id+1 FROM table t1 WHERE NOT EXISTS(SELECT * FROM table t2 WHERE t2.Id = t1.Id + 1) ORDER BY t1.Id 

Editar:

Para lidar com o caso especial em que o menor ID existente não é 1, aqui está uma solução feia:

 SELECT TOP 1 * FROM ( SELECT t1.Id+1 AS Id FROM table t1 WHERE NOT EXISTS(SELECT * FROM table t2 WHERE t2.Id = t1.Id + 1 ) UNION SELECT 1 AS Id WHERE NOT EXISTS (SELECT * FROM table t3 WHERE t3.Id = 1)) ot ORDER BY 1 

Se você classificá-los por ID numérico, o número que você está procurando será o primeiro para o qual a function ROW_NUMBER () não é igual ao ID.

Nenhuma menção de bloqueio ou simultaneidade em nenhuma das respostas até o momento.

Considere esses dois usuários adicionando um documento quase ao mesmo tempo:

 User 1 User 2 Find Id Find Id Id = 42 Id = 42 Insert (42..) Insert (42..) Error! 

Ou você precisa: a) Lidar com esse erro e contornar o loop novamente procurando pelo próximo Id disponível, OU b) Fazer um bloqueio no início do processo, para que apenas 1 usuário esteja procurando por IDs em um determinado momento

 SELECT TOP 1 t1.id+1 FROM mytable t1 LEFT OUTER JOIN mytable t2 ON (t1.id + 1 = t2.id) WHERE t2.id IS NULL ORDER BY t1.id; 

Esta é uma alternativa para as respostas usando subconsultas correlacionadas dadas por Jeffrey Hantlin e Darel Miller.

No entanto, a política que você está descrevendo não é uma boa ideia. Os valores de ID devem ser exclusivos, mas não devem ser obrigatórios para serem consecutivos.

O que acontece se você enviar um e-mail a alguém com um link para o documento 42 e, em seguida, excluir o documento posteriormente? Posteriormente, você reutiliza o id # 42 para um novo documento. Agora o destinatário do email seguirá o link para o documento errado !

 declare @value int select @value = case when @value is null or @value + 1 = idcolumn then idcolumn else @value end from table order by idcolumn select @value + 1 

Faz 1 varredura de tabela em vez de 2 varre uma correspondência de hash e uma junit como a resposta principal

Se existem lacunas na seqüência, você pode encontrar a primeira lacuna com algo parecido com isto:

 select top 1 (found.id + 1) nextid from (select id from items union select 0) found where not exists (select * from items blocking where blocking.id = found.id + 1) order by nextid asc 

Em outras palavras, encontre o menor ID cujo sucessor não exista e retorne esse sucessor. Se não houver intervalos, ele retornará um maior que o maior ID existente. Um ID de espaço reservado de 0 é inserido para garantir que os IDs iniciados por 1 sejam considerados.

Observe que isso levará pelo menos n log n time.

O Microsoft SQL permite o uso de uma cláusula from em uma instrução insert , portanto, você pode não precisar recorrer ao código procedural.

Existe uma razão para que seja o menor número possível? Por que você precisa preencher os buracos?

Edite no anúncio a resposta, já que é uma regra de negócios.

 DECLARE @counter int DECLARE @max SET @counter = 0 SET @max = SELECT MAX(Id) FROM YourTable WHILE @counter < = @max BEGIN SET @counter = @counter + 1 IF NOT EXISTS (SELECT Id FROM YourTable WHERE Id = @counter) BREAK END END 

(Eu não tenho um database à mão, então isso pode não ser 100% preciso, mas você deve ser capaz de obtê-lo de lá)

 select MIN(NextID) NextUsableID from ( select (case when c1 = c2 then 0 else c1 end) NextID from ( select ROW_NUMBER() over (order by record_id) c1, record_id c2 from myTable) ) where NextID > 0 

Aqui está uma abordagem simples. Pode não ser rápido. Não encontrará números perdidos no começo.

 SELECT MIN(MT1.MyInt+1) FROM MyTable MT1 LEFT OUTER JOIN MyTable MT2 ON (MT1.MyInt+1)=MT2.MyInt WHERE MT2.MyInt Is Null 

Você realmente deve tentar converter a coluna para IDENTIDADE. Primeiro, use o backup ROW_NUMBER para atualizar o ID do documento para que eles iniciem de 1 e até a contagem de documentos. Você deve fazê-lo em um WHILE no momento, porque se a coluna de número é usada como referência em outras tabelas (foreign keys), o SQL Server tentará atualizar as foreign keys e talvez falhar devido a conflitos. No final, apenas ative as especificações de identidade para a coluna.

🙂 É mais trabalho agora, mas vai poupar muitos problemas mais tarde.

Eu sei que esta resposta está atrasada, mas você pode encontrar o menor número não utilizado usando uma expressão de tabela recursiva:

 CREATE TABLE Test ( ID int NOT NULL ) --Insert values here ;WITH CTE AS ( --This is called once to get the minimum and maximum values SELECT nMin = 1, MAX(ID) + 1 as 'nMax' FROM Test UNION ALL --This is called multiple times until the condition is met SELECT nMin + 1, nMax FROM CTE WHERE nMin < nMax ) --Retrieves all the missing values in the table. Removing TOP 1 will --list all the unused numbers up to Max + 1 SELECT TOP 1 nMin FROM CTE WHERE NOT EXISTS ( SELECT ID FROM Test WHERE nMin = ID ) 

Vamos supor que seus IDs devam sempre começar com 1:

 SELECT MIN(a.id) + 1 AS firstfree FROM (SELECT id FROM table UNION SELECT 0) a LEFT JOIN table b ON b.id = a.id + 1 WHERE b.id IS NULL 

Isso lida com todos os casos em que posso pensar – incluindo nenhum registro existente.

A única coisa que não gosto nessa solução é que condições adicionais devem ser incluídas duas vezes, assim:

 SELECT MIN(a.id) + 1 AS firstfree FROM (SELECT id FROM table WHERE column = 4711 UNION SELECT 0) a LEFT JOIN table b ON b.column = 4711 AND b.id = a.id + 1 WHERE b.id IS NULL 

Observe também os comentários sobre bloqueio e simultaneidade – o requisito para preencher lacunas é, na maioria dos casos, um projeto defeituoso e pode causar problemas. No entanto, eu tinha uma boa razão para fazê-lo: os IDs devem ser impressos e typescripts por humanos e não queremos ter IDs com muitos dígitos depois de algum tempo, enquanto todos os baixos são gratuitos …

Eu enfrentei um problema semelhante e descobri isso:

 Select Top 1 IdGapCheck From (Select Id, ROW_NUMBER() Over (Order By Id Asc) AS IdGapCheck From dbo.table) F Where Id > IdGapCheck Order By Id Asc