ejemplos - select count sql server

Encuentra el número más pequeño no utilizado en SQL Server

Question

¿Cómo se encuentra el número más pequeño no utilizado en una columna de SQL Server?

Estoy a punto de importar una gran cantidad de registros grabados manualmente desde Excel a una tabla de SQL Server. Todos ellos tienen una identificación numérica (llamada número de documento), pero no fueron asignados secuencialmente por razones que ya no se aplican, lo que significa que a partir de ahora cuando mi sitio web registre un nuevo registro, debe asignarle el número de documento más pequeño posible ( mayor que cero) que aún no se ha tomado.

¿Hay alguna manera de hacerlo mediante SQL simple o es un problema para TSQL / código?

¡Gracias!

EDITAR

Un agradecimiento especial a WW por plantear el problema de la concurrencia. Dado que esta es una aplicación web, tiene múltiples subprocesos por definición y cualquiera que se enfrente a este mismo problema debería considerar bloquear un código o un nivel de DB para evitar un conflicto.

LINQ

FYI - esto se puede lograr a través de LINQ con el siguiente 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

Answer 1

¿Hay alguna razón por la cual tiene que ser el número más pequeño posible? ¿Por qué necesitas llenar los agujeros?

Edite la respuesta para publicarla, ya que es una regla comercial.

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

(No tengo un db a la mano, por lo que puede no ser 100% exacto, pero deberías poder obtenerlo desde allí)

Answer 2

Aquí hay un enfoque simple. Puede que no sea rápido. No encontrará números faltantes al principio.

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

Answer 3

Deberías tratar de convertir la columna a IDENTIDAD. BACKUP primero, luego use ROW_NUMBER para actualizar la ID del documento, de modo que empiecen desde 1 y hasta el recuento de documentos. Debería hacerlo en un MOMENTO uno por vez porque si la columna de número se utiliza como referencia en otras tablas (claves externas), SQL Server intentará actualizar las claves externas y puede fallar debido a conflictos. Al final solo habilita las especificaciones de identidad para la columna.

:) Es más trabajo ahora, pero te ahorrará muchos problemas más adelante.

Answer 4

Encuentra la primera fila donde no existe una fila con 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 manejar el caso especial donde el ID existente más bajo no es 1, aquí hay una solución fea:

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

Answer 5

Enfrenté un problema similar y se me ocurrió esto:

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

Answer 6

No hay mención de bloqueo o concurrencia en ninguna de las respuestas hasta el momento.

Considere estos dos usuarios agregando un documento casi al mismo tiempo:

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

Usted necesita: a) Manejar ese error e ir por el circuito de nuevo buscando el siguiente Id disponible, O b) Bloquear al inicio del proceso para que solo 1 usuario busque los Id en un momento determinado

Answer 7

Sé que esta respuesta es tardía, pero puede encontrar el número más pequeño sin usar mediante el uso de una expresión de tabla 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
)

Answer 8

Si hay lagunas en la secuencia, puede encontrar el primer espacio con algo como esto:

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

En otras palabras, encuentre la ID menor cuyo sucesor no exista y devuelva ese sucesor. Si no hay lagunas, devuelve una ID mayor que la mayor existente. Se inserta una ID de marcador de posición de 0 para garantizar que se consideren los ID que comienzan con 1.

Tenga en cuenta que esto llevará al menos n log n time.

Microsoft SQL permite el uso de una cláusula from en una instrucción de insert , por lo que es posible que no necesite recurrir a un código de procedimiento.

Answer 9

Si los clasifica por ID numérico, el número que está buscando será el primero para el cual la función ROW_NUMBER () no es igual a la ID.

Answer 10

Supongamos que sus ID siempre deberían comenzar con 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

Esto maneja todos los casos que puedo pensar, incluso sin registros existentes.

Lo único que no me gusta de esta solución es que las condiciones adicionales deben incluirse dos veces, así:

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

Tenga en cuenta también los comentarios sobre bloqueo y concurrencia: el requisito de rellenar huecos es, en la mayoría de los casos, un diseño incorrecto y puede causar problemas. Sin embargo, tenía una buena razón para hacerlo: las identidades deben ser impresas y mecanografiadas por los humanos y no queremos tener ID con muchos dígitos después de un tiempo, mientras que todas las más bajas son gratis ...

Answer 11

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 es una alternativa a las respuestas que usan subconsultas correlacionadas dadas por @Jeffrey Hantlin y @Darrel Miller.

Sin embargo, la política que describes no es realmente una buena idea. Los valores de ID deben ser únicos, pero no se debe exigir que sean consecutivos.

¿Qué sucede si envía un correo electrónico a alguien con un enlace al documento n. ° 42, y luego elimina el documento? Más tarde, volverá a usar la ID # 42 para un nuevo documento. ¡Ahora el destinatario del correo electrónico seguirá el enlace al documento equivocado !

Answer 12

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

¿Escanea 1 tabla en lugar de 2 escanea una coincidencia hash y una combinación como la respuesta principal?

Answer 13

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