none
Idices confusos en SQL Server RRS feed

  • Pregunta

  • Hola

    He notdo que una de mis trablas en Sql Sever 2014 me esta tirando los indices mal, brincan de numero demasiado...

    Adjunto una imagen para el que quiera ayudarme con esto...

    De antemano gracias por su ayuda...Imagen alternativa

    jueves, 21 de noviembre de 2019 20:47

Todas las respuestas

  • Probablemente la columna ID está autonumerada, es decir, tiene la propiedad IDENTITY. Estos saltos de numeración ocurren bajo ciertas condiciones, siendo la principal el reinicio del servicio SQL Server.

    Lectura sugerida: Marcas de seguimiento (trace flags). Consulte la información del TF 272.

     


    José Diz     Belo Horizonte, MG - Brasil     [query performance tuning: Porto SQL]


    Este conteúdo é fornecido sem garantias de qualquer tipo, seja expressa ou implícita.

    jueves, 21 de noviembre de 2019 22:33
  • Hola rodolopa:

    La propiedad identity no garantiza:

    • El valor único (pero si en combinación con unique o primary key)
    • Valores consecutivos
    • Valores consecutivos después de un reinicio

    Identity

    https://docs.microsoft.com/es-es/sql/t-sql/statements/create-table-transact-sql-identity-property?view=sql-server-ver15

    create table EJIDENTITY (id int identity(1,1) primary key, valor char(1))
    go
    INSERT INTO EJIDENTITY
    (VALOR)
    VALUES
    ('A')
    GO
    BEGIN TRAN
    INSERT INTO EJIDENTITY (VALOR)
    VALUES
    ('B')
    ROLLBACK TRAN
    GO
    INSERT INTO EJIDENTITY (valor)
    values
    ('B')
    GO
    SELECT * FROM EJIDENTITY

    Salida

    Como ves, simplemente con deshacer una transacción se produce un salto.

    Como te indica José Diz, si desactivas el Flag 272, conseguirás que los saltos que se produzcan normalmente por reinicio del servidor, no sean de 1000 filas, a costa de rendimiento, ya que ese flag, lo que hace es guardar en cache 1000 números para futuras inserciones.

    SHUTDOWN WITH NOWAIT;
    GO

    Inicio el Servicio nuevamente.

    INSERT INTO EJIDENTITY (valor)
    values
    ('C')
    GO
    SELECT * FROM EJIDENTITY

    Flag 272

    https://www.dfarber.com/computer-consulting-blog/articles/how-to-solve-identity-problem-in-sql-2012/

    viernes, 22 de noviembre de 2019 4:52
  • Gracias por responder

    Pero yo quisiera que me conserve la numeracion...

    Hay alguna forma de evitar que esto pase ?

    Necesito que el incrementosea n+1 no importa si se reinicia el servidor...

    Se puede ?

    viernes, 22 de noviembre de 2019 15:28
  • Gracias por responder

    Pero yo quisiera que me conserve la numeracion...

    Hay alguna forma de evitar que esto pase ?

    Necesito que el incrementosea n+1 no importa si se reinicia el servidor...

    Se puede ?

    viernes, 22 de noviembre de 2019 15:28
  • Necesito que el incrementosea n+1 no importa si se reinicia el servidor...
    Se puede ?

    Ni IDENTITY ni SEQUENCE le garantizan una secuencia sin saltos. En este caso, debería implementar su rutina de generación de valor secuencial: "Si no es aceptable que haya espacios, la aplicación debe usar mecanismos propios para generar valores de clave".

    Lectura sugerida:


    José Diz     Belo Horizonte, MG - Brasil     [query performance tuning: Porto SQL]


    Este conteúdo é fornecido sem garantias de qualquer tipo, seja expressa ou implícita.

    • Editado José Diz viernes, 22 de noviembre de 2019 15:55
    viernes, 22 de noviembre de 2019 15:52
  • Hola rodolopa:

    Si se puede de diversas formas.

    /* TABLA CON LOS DATOS */
    CREATE TABLE tabla(ID INT, valor CHAR(1))
    GO
    /* TABLA AUXILIAR PARA TENER EL ID MAXIMO Y ASÍ NO BLOQUEAR LA TABLA PRINCIPAL */
    CREATE TABLE auxiliarMax (Id INT PRIMARY KEY)
    GO
    INSERT INTO dbo.auxiliarMax(Id)
    values
    (0);
    GO
     /* PROCEDIMIENTO QUE DEVUELVE EL SIGUIENTE ID */
    CREATE PROCEDURE [dbo].[MAXIMO_ID]
    (
    	@ID INT OUTPUT
    )
    AS
    BEGIN TRY
    	SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
    	BEGIN TRANSACTION 
    
    	SET @ID = (SELECT ISNULL(MAX(ID),0) + 1 FROM auxiliarMax);
    
    	UPDATE auxiliarMax SET ID = @ID;
    
    	COMMIT TRANSACTION
    END TRY
    BEGIN CATCH
    	IF @@TRANCOUNT > 1 
    		ROLLBACK TRANSACTION;
    	THROW;
    END CATCH
    
    RETURN
    GO
    
    /* PROCEDIMIENTO PARA INSERTAR NORMAL */
    CREATE PROCEDURE SP_INSERTAR 
    (
    @VALOR VARCHAR(10)
    )
    AS
    BEGIN
    BEGIN TRY
    	BEGIN TRANSACTION;
    	DECLARE @ID INT;
    	EXEC [dbo].[MAXIMO_ID] @ID OUTPUT
    	INSERT INTO tabla (ID, VALOR)
    	VALUES
    	(@ID, @VALOR);
    	COMMIT TRANSACTION;
    
    END TRY
    BEGIN CATCH
    	IF @@TRANCOUNT>0
    		ROLLBACK TRANSACTION;
    	THROW;
    END CATCH
    END
    GO
    EXEC SP_INSERTAR 'A';
    EXEC SP_INSERTAR 'B';
    EXEC SP_INSERTAR 'C';
    EXEC SP_INSERTAR 'D';
     GO
     SELECT * FROM TABLA

    Así puedes disponer de el siguiente valor, pasando siempre por el procedimiento de insertar y sin establecer bloqueos en la tabla de tus datos.

    Esto es independiente del reinicio del servidor.

    Eso sí, si deshaces una transacción entonces tendrás ese salto.

    Otra opción

    CREATE TABLE EJEMPLO2(ID INT PRIMARY KEY, VALOR VARCHAR(4))
    GO
    INSERT INTO EJEMPLO2 (ID, VALOR)
    VALUES
    (0,'');
    GO
    
    
    CREATE PROCEDURE SP_EJEMPLO2 
    (
    	@VALOR VARCHAR(4)
    )
    AS
    BEGIN
    BEGIN TRY
    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
    
    BEGIN TRANSACTION
    
    INSERT INTO EJEMPLO2 (ID, VALOR)
    VALUES
    ((SELECT MAX(ID)+1 FROM EJEMPLO2), @VALOR);
    COMMIT TRANSACTION
    END TRY
    BEGIN CATCH
    	IF @@TRANCOUNT > 0 
    		ROLLBACK TRANSACTION;
    	THROW;
    END CATCH
    END
    GO
    EXEC SP_EJEMPLO2 'A'
    EXEC SP_EJEMPLO2 'B'
    EXEC SP_EJEMPLO2 'C'
    EXEC SP_EJEMPLO2 'D'
    GO
    SELECT * FROM EJEMPLO2
    

    Estas són dos de las posibles soluciones.

    viernes, 22 de noviembre de 2019 17:33