none
Sugerencias para manejar un campo incremental y consecutivo por llave foránea en una tabla RRS feed

  • Pregunta

  • Recurro a ustedes en busca de sugerencias:

    Les cuento a modo de ejemplo, tengo una tabla con tres campos ID (Primary Key), Folio y ID_Grupo (Llave foránea). La llave primaria es autoincremental, el campo folio debe registrar un número consecutivo  por cada ID_Grupo, algo como se muestra en la siguiente imagen:

    Lo que quisiera saber es si hay alguna forma de controlar el consecutivo del campo Folio por cada ID_GRUPO desde la base de datos (Cómo lo hace el identity),  o si es  más fácil manejarlo desde la interfaz de usuario por medio de una consulta SELECT MAX(Folio) where ID_GRUPO=X...  y a partir del ultimo folio registrado sumarle un 1

    Agradecería pudieran orientarme sobre cuál es la forma más adecuada de manejar este campo.  

    lunes, 26 de marzo de 2018 17:55

Respuestas

  • Hola Zarpis:

    Sin contradecir, ni un solo punto de lo que te han dicho, yo no tomaría jamás dejar la decisión en capa cliente, explicandolo de un modo sencillo, puede haber millones de unidades de tiempo, entre la solicitud de id, y la consiguiente insercción, sin saberlo, porque por ejemplo nuestro servidor web esta tocándose la gaita, y el sql atiende otras muchas peticiones, mientras.

    Te aporto mi granito de arena, ya que al menos esta todo bien juntito y en una transacción.

    El primer punto es que el grupo ya lo tienes que saber.

    CREATE PROCEDURE [dbo].[MAXIMO_M]
    (
    	@GRUPO INT /*PARAMETRO RECIBIR*/
    )
    AS
    	
    	BEGIN TRANSACTION
    
    	INSERT INTO tabla3campos /*NOMBRE QUE LE HE DADO A LA TABLA */
    		SELECT ISNULL(MAX(FOLIO),0) + 1 , @GRUPO FROM tabla3campos WHERE GRUPO = @GRUPO
    	
    		if (@@error<>0)
      			ROLLBACK TRANSACTION
    		ELSE
    			COMMIT TRANSACTION
    
    RETURN

    Supongo que para saltarte esto tienes que tener mucha concurrencia, y si tienes tanta concurrencia, este no será tu problema. (Tendrás otros mejores).

    Un saludo

    • Marcado como respuesta Zarpis miércoles, 28 de marzo de 2018 18:01
    lunes, 26 de marzo de 2018 21:38
  • Si es simplemente por los valores de folio importados, puede simplemente agregar una constante al ROW_NUMBER para que los valores de folio generados inicien más allá del valor más alto importado.  Puede hacer una pequeña tabla que indique esta constante para cada ID_Codigo que tenga.  Los que no estén listados se asume constante cero.  La modificación es sencilla de hacer.

    Ejemplo:

    ID_Codigo 1 tiene un ID de folio máximo de 1112 y existen 1002 registros.  La vista (o CTE) entonces calcularía que el siguiente número de folio sería el 1003, pero está fallando porque debería ser 1113.  Entonces la constante para este ID_Codigo sería de 10.

    Entonces necesitamos una tabla muy sencilla:

    Create Table dbo.tblDesfases
    (
        ID_Codigo int Not Null Primary Key Clustered
        , Valor int Not Null Check (Valor >= 0)
    );


    La vista se modifica así:

    Create View dbo.vwTabla
    As
    Select
        t.PK
        , t.ID_Grupo
        , Row_Number() Over (Partition By t.ID_Grupo Order By t.PK Asc) + Coalesce(d.Valor, 0) As Folio
    From
        dbo.tabla As t
        Left Join
        dbo.tblDesfases As d
        On
        t.ID_Codigo = d.ID_Codigo
    ;
    Go

    Y la versión con CTE sería:

    Create Procedure dbo.uspInsertarNuevo
        ... --Parámetros para los valores de las distintas columnas, incluido uno llamado @idGrupo para ID_Grupo.
        , @id int output --o bigint o lo que se necesite para devolver el PK del nuevo registro
    As
    Begin
        Set NoCount On;
    
        Declare @errorNumber int = 0;
    
        Begin Tran; --Meramente para hacer atómicas las modificaciones, no para evitar problemas de concurrencia.
    
        Begin Try
            Insert Into dbo.tabla (....)
            Values (....@idGrupo, ....);
    
            Set @id = Scope_Identity();
    
            With calculoFolios As
            (
                Select
                    t.PK
                    , Row_Number() Over (Partition By t.ID_Grupo Order By t.PK Asc) + Coalesce(d.Valor, 0) As [FolioCalculado]
                From
                    dbo.tabla As t
                    Left Join
                    dbo.tblDesfases As d
                    On t.ID_Codigo = d.ID_Codigo
            )
            Update
                dbo.Tabla
            Set
                Folio = cf.FolioCalculado
            From
                dbo.Tabla As t
                Inner Join
                calculoFolios As cf
                On t.PK = cf.PK
            Where
                t.PK = @id
            ;
            Commit Tran;
        End Try
        Begin Catch
            Set @errorNumber = Error_Number();
            Rollback Tran;
        End Catch
        Return @errorNumber;
    End
    Go

    Ah, y si le preocupa el llenado de la tabla de desfases, debería ser muy sencillo de llenar en una sola sentencia (después de importar los datos):

    With agrupaciones As
    (
        Select
            ID_Grupo
            , Max(Folio) As [MaxFolio]
            , Count(Folio) As [TotalFolios] --O tal vez Count(*).  Depende de sus datos.
        From
            dbo.Tabla
        Group By
            ID_Grupo
    )
    Insert Into dbo.tblDesfases
    Select
        ID_Grupo
        , MaxFolio - TotalFolios
    From
        agrupaciones
    Where --Opcional.  Simplemente para no escribir ceros.
        MaxFolio <> TotalFolios
    ;
    Ah, y le agregué un CHECK a la tablita de control para asegurarnos de que no hay constantes negativas.


    Jose R. MCP
    Code Samples


    • Editado webJose martes, 27 de marzo de 2018 19:42
    • Marcado como respuesta Zarpis miércoles, 28 de marzo de 2018 18:03
    martes, 27 de marzo de 2018 19:36

Todas las respuestas

  • Saludos,

    Consultas y tomar el dato pero en alta concurrencia puede que el orden no sea respetado, es bastante difícil poder contraer un control ordenado (puedes hacer un incremental y que no se repita pero que sea ordenado es bastante difícil) tal vez alguien te ofrezca una solución pero en mi caso consultaría y haría el update pero no es ideal.


    Blog: www.sqlservertoolbox.blogspot.com.mx

    lunes, 26 de marzo de 2018 20:40
  • La solucion que planteas de hecho tiene una tendencia a fallar de manera horrible cuando tienes alta concurrencia, como bien dice Enrique, puedes encontrar muchos blogs con ejemplos al respecto, sin embargo, siempre que veo este tipo de consultas hago la misma pregunta, existe una obligatoriedad de Ley por las que requieras que dicha columna sea consecutiva y sin ningun gap (por ejemplo en Italia existe legislacion al respecto sobre codigos de Factura consecutivos), en caso que la respuesta sea no, te podria decir que incluso pudieras prescindir de ese campo y obtenerlo al vuelo a traves de Windows Function en caso que algun reporte o consulta lo requiriera.

    "Oh, the wind, the wind is blowing,through the graves the wind is blowing,Freedom soon will come; then well come from the shadows".The Partisan(Leonard Cohen) Email: me[at]geohernandez.net Blog:www.geohernandez.net

    lunes, 26 de marzo de 2018 20:46
  • Hola Zarpis:

    Sin contradecir, ni un solo punto de lo que te han dicho, yo no tomaría jamás dejar la decisión en capa cliente, explicandolo de un modo sencillo, puede haber millones de unidades de tiempo, entre la solicitud de id, y la consiguiente insercción, sin saberlo, porque por ejemplo nuestro servidor web esta tocándose la gaita, y el sql atiende otras muchas peticiones, mientras.

    Te aporto mi granito de arena, ya que al menos esta todo bien juntito y en una transacción.

    El primer punto es que el grupo ya lo tienes que saber.

    CREATE PROCEDURE [dbo].[MAXIMO_M]
    (
    	@GRUPO INT /*PARAMETRO RECIBIR*/
    )
    AS
    	
    	BEGIN TRANSACTION
    
    	INSERT INTO tabla3campos /*NOMBRE QUE LE HE DADO A LA TABLA */
    		SELECT ISNULL(MAX(FOLIO),0) + 1 , @GRUPO FROM tabla3campos WHERE GRUPO = @GRUPO
    	
    		if (@@error<>0)
      			ROLLBACK TRANSACTION
    		ELSE
    			COMMIT TRANSACTION
    
    RETURN

    Supongo que para saltarte esto tienes que tener mucha concurrencia, y si tienes tanta concurrencia, este no será tu problema. (Tendrás otros mejores).

    Un saludo

    • Marcado como respuesta Zarpis miércoles, 28 de marzo de 2018 18:01
    lunes, 26 de marzo de 2018 21:38
  • Agradezco a todos sus respuestas.

    A mi me queda muy claro que manejar un folio consecutivo desde la interfaz de usuario es todo un riesgo, de hecho en lo personal TODAS las llaves primarias las declaro de tal forma que sean autoincrementales. Pero en este caso, debo tener un folio aparte para control y referencia del usuario.  Es por lo anterior que pedí sugerencias al respecto. Quisiera disminuir dentro de lo posible problemas de concurrencia.

    Javi Fernández F: Apliqué tu sugerencia y de hecho funciona bien, solo algunas dudas? ¿Qué riesgos implican trabajar con este store procedure? ¿Qué desventajas pudiera tener? ¿Me libro del problema de concurrencia?

    Saludos

    lunes, 26 de marzo de 2018 22:48
  • Hola Zarpis:

    El único problema que yo le veo es, que si aumentas la tabla en anchura, colocando más campos, y no puedes hacer la insert directamente, tienes que hacer una select porque desconoces algún campo. Personalmente, la solución pasa por dejar esta tabla siempre como generadora de identidades, y si necesitas más campos, entonces vas a otra tabla, alimentando esta únicamente esos datos (folio,grupo).

    Yo he tenido que implementar esta solución con un problema similar, y desde su aplicación, nunca he visto un error, dado que la generación del Folio se produce en la propia insercción.

    Como mejora, supongo que te será interesante, pasarle al procedure parametros como out, y alimentarlos en el procedure y así ya dispones de la clave PK insertada, etc.

    Saludos

    martes, 27 de marzo de 2018 5:14
  • Javi, ese select max(isnull) puede dar problemas serios de concurrencia,

    Yo cuando tengo que implementar estas cosas las hago de la siguiente forma

    créate table contadores(idgrupo int not null primary key, folio int)

    esta tabla, la inicializaría así (Asegurándome que no hay nadie en este momento o generando un bloqueo exclusivo de tabla)

    insert into contadores

    select idgrupo,max(folio) from tutabla group by idgrupo

    para optener el mayor ahora

    begin tran

    declare @siguiente int

    declare @t table (folio int)

    update contadores

    set folio=folio+1 

    where grupo=@idgrupo

    output inserted.folio into @t

    select @siguiente=folio from @t

    commit

    El código está escrito de memoria por lo que puede fallar.


    Comparte lo que sepas, aprende lo que no sepas (FGG)
    portalSQL
    El rincón del DBA

    • Propuesto como respuesta Willams Morales martes, 27 de marzo de 2018 14:10
    martes, 27 de marzo de 2018 6:26
    Moderador
  • Sigo diciendo que esa solución tiene muchos problemas de concurrencia particularmente en entornos exigentes, porque dos select max devolverán el mismo valor a dos conexiones distintas, los bloqueos que se generan son compartidos y por tanto pueden  generar ese problema.

    Zarpis, revisa el código que te escribí, y si te vale.. estupendo.


    Comparte lo que sepas, aprende lo que no sepas (FGG)
    portalSQL
    El rincón del DBA

    • Propuesto como respuesta Willams Morales martes, 27 de marzo de 2018 14:10
    martes, 27 de marzo de 2018 6:28
    Moderador
  • Le doy una solución que no padece de nada (o casi nada):

    Create View dbo.vwTabla
    As
    Select
        PK
        , ID_Grupo
        , Row_Number() Over (Partition By ID_Grupo Order By PK Asc) As Folio
    From
        dbo.tabla
    ;
    Go

    En otras palabras:  NO almacene Folio.  La columna Folio deja de existir en la tabla.  Cuando haga SELECT's, hágalo desde la vista que le propongo, que sí tiene la columna Folio.  El truco aquí es que Folio es un valor calculado siempre.  Lo interesante es que como está ordenado por ID y el ID es autonumérico, la probabilidad de que se calcule un folio distinto para un mismo registro en 2 ocasiones diferentes es muy baja.  En realidad es cero siempre y cuando nunca altere el autonumérico, y alterar el autonumérico es algo que se ve rara vez.


    Jose R. MCP
    Code Samples

    martes, 27 de marzo de 2018 6:39
  • esa solución no mantendrá los números, y pretenden ser una fk. no le vale. (Creo yo)

    Comparte lo que sepas, aprende lo que no sepas (FGG)
    portalSQL
    El rincón del DBA

    martes, 27 de marzo de 2018 6:41
    Moderador
  • Sí mantiene los números mientras no se juegue con la columna autonumérica.

    Jose R. MCP
    Code Samples

    martes, 27 de marzo de 2018 6:46
  • Pero no puedes crear una fk a una vista. y ese mientras no permitiría ponerle cosas como el update cascade. 

    Yo creo que no es una solución valida


    Comparte lo que sepas, aprende lo que no sepas (FGG)
    portalSQL
    El rincón del DBA

    martes, 27 de marzo de 2018 6:50
    Moderador
  • Como aclaración a lo que aporta Javi:  Recordemos que en SQL Server el nivel de aislamiento de transacción por defecto es READ COMMITTED.  Por lo tanto, iniciar una transacción en este nivel no evitará duplicidad del valor de folio en caso de concurrencia.  Antes de iniciar la transacción, hay que elevar el nivel de aislamiento.

    Set Transaction Isolation Level Repeatable Read; --O superior.
    Begin Tran;
    ...
    
    --Y luego devolverla a toda costa porque terminado el PA no se devuelve automáticamente.
    --Idealmente, debería haber un BEGIN TRY..END TRY BEGIN CATCH..END CATCH.
    Set Transaction Isolation Level Read Committed;
    


    Jose R. MCP
    Code Samples

    martes, 27 de marzo de 2018 6:54
  • Pero no puedes crear una fk a una vista. y ese mientras no permitiría ponerle cosas como el update cascade. 

    Yo creo que no es una solución valida


    Comparte lo que sepas, aprende lo que no sepas (FGG)
    portalSQL
    El rincón del DBA

    Pues sí, en el caso de que Folio tuviera que ser parte de una clave, la solución no es de fiar.  Sin embargo, la vista puede ser usada como base para el cálculo del nuevo número de folio, ya sea como vista (así como la puse), o en un CTE.  De esa manera no habría necesidad de tener una tabla de contadores por separado.  ¿La ganancia?  No estoy nada seguro pues no me declaro experto en SQL Server en lo absoluto.  Habría que correr pruebas.

    Jose R. MCP
    Code Samples

    martes, 27 de marzo de 2018 6:58
  • Bueno, ahora que lo pienso, la vista ayuda a eliminar la concurrencia, aún si tuviéramos que escribir el folio a la tabla. :-)

    Paso 1:  Insertar nuevo registro, obviamente sin folio.  Insert con ID_Grupo y las demás columnas que hayan en la tabla.

    Paso 2:  Hacer un update con la vista que propongo (o en forma de CTE) y simplemente copiar el folio calculado.  El JOIN sería basado en el PK únicamente, así que no importa si hay 1000 usuarios insertando registros a la vez.  No habrá duplicidad del valor de folio, no hay necesidad de una transacción (excepto para hacer atómico el INSERT-UPDATE) y la concurrencia no se ve afectada en lo absoluto.

    Por lo tanto, mi "respuesta final" sería mi vista si es que "Folio" no participa en ninguna clave, o el PA que muestro a continuación si definitivamente necesitamos Folio en la tabla.

    Create Procedure dbo.uspInsertarNuevo
        ... --Parámetros para los valores de las distintas columnas, incluido uno llamado @idGrupo para ID_Grupo.
        , @id int output --o bigint o lo que se necesite para devolver el PK del nuevo registro
    As
    Begin
        Set NoCount On;
    
        Declare @errorNumber int = 0;
    
        Begin Tran; --Meramente para hacer atómicas las modificaciones, no para evitar problemas de concurrencia.
    
        Begin Try
            Insert Into dbo.tabla (....)
            Values (....@idGrupo, ....);
    
            Set @id = Scope_Identity();
    
            With calculoFolios As
            (
                Select
                    PK
                    , Row_Number() Over (Partition By ID_Grupo Order By PK Asc) As [FolioCalculado]
                From
                    dbo.tabla
            )
            Update
                dbo.Tabla
            Set
                Folio = cf.FolioCalculado
            From
                dbo.Tabla As t
                Inner Join
                calculoFolios As cf
                On t.PK = cf.PK
            Where
                t.PK = @id
            ;
            Commit Tran;
        End Try
        Begin Catch
            Set @errorNumber = Error_Number();
            Rollback Tran;
        End Catch
        Return @errorNumber;
    End
    Go
    


    Jose R. MCP
    Code Samples

    martes, 27 de marzo de 2018 7:17
  • En este caso, tener un numero de fila no es el objetivo, es una necesidad para poder tener integridad en la base de datos, (o eso creo yo) y no es un tema de expertise en base de datos, sino de diseño. 

    Tu ejemplo estaría bien para hacer un report, o simplemente con propósitos informativos, pero creo que le falta consistencia para diseño de base de datos y garantizar esa integridad, además del consumo de recursos, cada vez que hagas ese select se recalcula el valor, en tablas grandes puede tener un coste importante, particularmente si los índices no son adecuados. 

    El select max, tiene un problema con la concurrencia, dos sesiones pueden devolver el mismo y entonces hay que recuperarse de un error.

    El ejemplo que yo propongo genera un bloqueo de actualización por lo que garantiza la "zona crítica", que es un ejemplo muy común en los problemas de concurrencia, por eso es por lo que lo propongo.

    En cualquier caso, seguro que a alguien le viene bien esta discusión.

    SAludos 


    Comparte lo que sepas, aprende lo que no sepas (FGG)
    portalSQL
    El rincón del DBA

    • Propuesto como respuesta Willams Morales martes, 27 de marzo de 2018 14:13
    martes, 27 de marzo de 2018 7:30
    Moderador
  • Tienes un error de concepto. Eso no arregla el problema de concurrencia

    eso solo genera bloqueos compartidos, no hay nivel de aislamiento que haga que lectores bloqueen a lectores (A dios gracias)

    se puede intentar usar updlock como sentencia with pero ... sigo creyendo que es mas elegante tener una tabla exgterna


    Comparte lo que sepas, aprende lo que no sepas (FGG)
    portalSQL
    El rincón del DBA

    • Propuesto como respuesta Willams Morales martes, 27 de marzo de 2018 14:13
    martes, 27 de marzo de 2018 7:32
    Moderador
  • En este caso, tener un numero de fila no es el objetivo, es una necesidad para poder tener integridad en la base de datos, (o eso creo yo) y no es un tema de expertise en base de datos, sino de diseño. 

    Tu ejemplo estaría bien para hacer un report, o simplemente con propósitos informativos, pero creo que le falta consistencia para diseño de base de datos y garantizar esa integridad, además del consumo de recursos, cada vez que hagas ese select se recalcula el valor, en tablas grandes puede tener un coste importante, particularmente si los índices no son adecuados. 

    El select max, tiene un problema con la concurrencia, dos sesiones pueden devolver el mismo y entonces hay que recuperarse de un error.

    El ejemplo que yo propongo genera un bloqueo de actualización por lo que garantiza la "zona crítica", que es un ejemplo muy común en los problemas de concurrencia, por eso es por lo que lo propongo.

    En cualquier caso, seguro que a alguien le viene bien esta discusión.

    SAludos 


    Comparte lo que sepas, aprende lo que no sepas (FGG)
    portalSQL
    El rincón del DBA

    Hmm, no entiendo a qué se refiere con "tener un número de fila no es el objetivo".  Solamente porque la función que utilizo se llama Row_Number() no quiere decir que sea "simplemente un número de fila".  ¿No es acaso un autonumérico un número arbitrario que el 99.9% de los casos lo que hace es simplemente "enumerar las filas", aumentando su valor en 1?

    Otro punto que me deja duda es el asunto del recálculo y las tablas grandes.  Nuevamente:  No soy experto en SQL, pero imagino yo que SQL Server creará un plan optimizado en el WHERE de la consulta.  Este WHERE limita a un único ID_Grupo, así que si SQL Server es tan inteligente como creo yo que es, el plan de ejecución primero filtrará la tabla para los ID_Grupo que corresponden al único que yo quiero, reduciendo drásticamente la cantidad de registros a manejar y por lo tanto el esfuerzo del cálculo.  En todo caso imagino que esto puede comprobarse examinando el plan de ejecución, y en el peor de los casos donde SQL Server no resultara tan inteligente como yo hubiera querido, el CTE puede contener la cláusula WHERE ID_Grupo = @idGrupo, forzando así el plan de ejecución deseado.

    Así que en resumen, sigo apoyando mis soluciones al 100%.  Mientras no se juegue con la clave primaria, yo propongo que mi solución será sólida al 100%.


    Jose R. MCP
    Code Samples

    martes, 27 de marzo de 2018 7:40
  • No me entiendas mal Jose R. 

    No quiero decir que no me guste tu aporte, ni le resto importancia técnica, nada más lejos de mi intención.

    Sobre el optimizador, si, supongo que hará eso.. o no lo hará... tanto da, en cualquier caso es un tema que se puede optimizar creando índices.

    Lo que te insisto que no puedes hacer es crear claves ajenas, y por tanto no puedes garantizar la integridad.

    Imagina que se borra un registro en la mitad de un grupo, (no parece raro), a partir de ese momento, todos los siguientes su row_number() será 1 menos, y todas las tablas que apunten a esta estarán apuntando a un valor erróneo, a eso me refiero con perdida de identidad, y por eso  comento que no lo veo adecuado en este caso. Dicho esto.... yo no tengo ni idea de que quiere hacer la persona que preguntó, quizá tu solución le valga, o quizá no..

    En cualquier caso, encuentro este debate bastante enriquecedor.


    Comparte lo que sepas, aprende lo que no sepas (FGG)
    portalSQL
    El rincón del DBA

    • Propuesto como respuesta Willams Morales martes, 27 de marzo de 2018 14:14
    martes, 27 de marzo de 2018 7:55
    Moderador
  • Hola a todos:

    Mola, debate....

    Estoy de acuerdo con todos, pero.....

    Como toda solución todo tienes sus peros.

    Evidentemente, puede ocurrir un problema de concurrencia, SI.

    Cuantas posibilidades hay, creo que muy pocas. Dado que la insert y la Select van en el mismo paquete. Remarco que es cierto, pero en fuego real yo no las he visto. Si sometiendo al procedure a varios bucles concurrentes, si.

    La solución de Miguel de la Update, es correcta, dado que la update se llevará a cabo solo si no hay concurrencia, el agujero es, que hay que insertar un nuevo valor en contadores, cuando se inserte un grupo. Un trigger o similar bastaría. (Me gusta).

    La solución de WebJose, es muy buena, pero si haces un test de stress sobre la misma, no es rápida.Pero me gusta mucho el debate, porque si los que diseñan esto no han puesto todavía una solución perfecta para el problemilla, es porque todas tienen sus peros.

    Más ideas que mola.

    Saludos

    martes, 27 de marzo de 2018 8:00
  • Tienes un error de concepto. Eso no arregla el problema de concurrencia

    eso solo genera bloqueos compartidos, no hay nivel de aislamiento que haga que lectores bloqueen a lectores (A dios gracias)

    se puede intentar usar updlock como sentencia with pero ... sigo creyendo que es mas elegante tener una tabla exgterna


    Comparte lo que sepas, aprende lo que no sepas (FGG)
    portalSQL
    El rincón del DBA

    No exactamente.  Revisando las definiciones de los niveles de aislamiento, simplemente escogí el incorrecto.  Para hacer autonuméricos "manualmente", necesitamos el nivel SERIALIZABLE.  Este sí impondrá bloqueos apropiados.

    Entonces en mis aclaraciones anteriores, léase SERIALIZABLE en lugar de REPEATABLE READ.


    Jose R. MCP
    Code Samples

    martes, 27 de marzo de 2018 8:03
  • No me entiendas mal Jose R. 

    No quiero decir que no me guste tu aporte, ni le resto importancia técnica, nada más lejos de mi intención.

    Sobre el optimizador, si, supongo que hará eso.. o no lo hará... tanto da, en cualquier caso es un tema que se puede optimizar creando índices.

    Lo que te insisto que no puedes hacer es crear claves ajenas, y por tanto no puedes garantizar la integridad.

    Imagina que se borra un registro en la mitad de un grupo, (no parece raro), a partir de ese momento, todos los siguientes su row_number() será 1 menos, y todas las tablas que apunten a esta estarán apuntando a un valor erróneo, a eso me refiero con perdida de identidad, y por eso  comento que no lo veo adecuado en este caso. Dicho esto.... yo no tengo ni idea de que quiere hacer la persona que preguntó, quizá tu solución le valga, o quizá no..

    En cualquier caso, encuentro este debate bastante enriquecedor.


    Comparte lo que sepas, aprende lo que no sepas (FGG)
    portalSQL
    El rincón del DBA

    No entiendo mal.  Más bien le pido que no me entienda mal a mí.  Yo simplemente defendía mi posición de manera saludable y asertiva.  Cuando se es asertivo, a veces da la apariencia de descortés.

    Lo que usted comenta de borrar registros es algo que definitivamente no me pasaba por la cabeza.  En mis 15 años de profesión nunca he borrado registros de producción y por eso ni siquiera me pasó por la cabeza algo como eso.  Claro, tiene usted razón:  Si se permite el borrado de registros, la solución que propongo no es viable justamente por la razón que usted menciona.  En mis 15 años de carrera solamente he programado el borrado de registros en relaciones muchos a muchos (o sea, la eliminación de la relación entre registros).


    Jose R. MCP
    Code Samples

    martes, 27 de marzo de 2018 8:09
  • no es muy común borrar, menos de modo programática, pero si es muy común que los usuarios se equivoquen y que el dba borre .... por eso mi reticencia.

    Saludos! Buen debate


    Comparte lo que sepas, aprende lo que no sepas (FGG)
    portalSQL
    El rincón del DBA

    martes, 27 de marzo de 2018 8:13
    Moderador
  • Yo pongo la alternativa de un campo para desactivar el registro.  Luego todas las consultas deben filtrar por este campo para así hacer de cuenta que está eliminado.  Ya cuando programo en C# los objetos entidad lo tengo todo muy bien planeado:

    Clase NotifyPropertyChanged (implementa INotifyPropertyChanged)
        -> Clase DtoConstructible (agrega bases para la construcción del objeto a través de un DTO)
            -> Clase Entity (agrega ID tipo long -bigint- y propiedades que indican si el registro es alterado o si es nuevo)
                -> Clase DeactivatableEntity (agrega la propiedad IsActive para desactivar un registro en vez de borrar)
    Supongo que cada quién desolla el gato de manera diferente.

    Jose R. MCP
    Code Samples

    martes, 27 de marzo de 2018 8:21
  • Creeme , Serializable tampoco funciona, por que insisto, lectores no bloquean lectores. 

    Comparte lo que sepas, aprende lo que no sepas (FGG)
    portalSQL
    El rincón del DBA

    martes, 27 de marzo de 2018 9:16
    Moderador
  • Creeme , Serializable tampoco funciona, por que insisto, lectores no bloquean lectores. 

    Comparte lo que sepas, aprende lo que no sepas (FGG)
    portalSQL
    El rincón del DBA

    Bueno, si ese fuere el caso tendré que probar.  Es hora de dormir para mí, así que dejaré este asunto de lado por ahora.  Por lo menos este detalle no debería cambiar la viabilidad de mi propuesta, salvo por el caso de eliminación de registros que ya fue expuesto y que no tiene relación con este punto.

    Jose R. MCP
    Code Samples

    martes, 27 de marzo de 2018 9:51
  • Saludos, dormir es malo me pierdo la diversión.

    Lo que comenta Miguel es lo mismo que pensé con el uso de vista pones en peligro la integridad de la base usando este método, aunque debo admitir que es un método elegante y teóricamente funcional en un mundo ideal, posiblemente la manera que me enseñaron y algunas experiencias, pero no supongo ya jamas que mis clientes haran happy path y no borraran o algo, es un poco inocente pensar asi, y aunque no se deberia de hacer lamentablemente lo hacen, en vez de deshabilitar registros ya no usados los borran y esto crearia conflictos. 


    Blog: www.sqlservertoolbox.blogspot.com.mx

    martes, 27 de marzo de 2018 15:43
  • Algo muy sencillo de hacer es un INSTEAD OF DELETE trigger que simplemente haga un RAISERROR cuando se trate de borrar.  No es cuestión de ser ingenuo y más bien de ser ingenioso.  Trabajamos con lo que tenemos a disposición.

    Jose R. MCP
    Code Samples

    martes, 27 de marzo de 2018 18:51
  • Saludos,

    Estas implicando que el cliente permita esto en primer lugar. Pero bueno como digo la idea es buena a mi personalmente no me gusta y la base no debería tener tantos controles de flujo en mi opinión. 


    Blog: www.sqlservertoolbox.blogspot.com.mx

    martes, 27 de marzo de 2018 18:56
  • Si tuviéramos la atomicidad de un IDENTITY expuesta, no habría que hacer tanto salto, estamos de acuerdo en eso.  Lo que trato de decir es que buscamos la mejor solución con lo que tenemos a mano.  Si lo que podemos tener a mano es ROW_NUMBER() + INSERT-UPDATE + INSTEAD OF DELETE TRIGGER, ¿qué se le va a hacer?  Es lo que es.  O logramos el objetivo, o no.  Esa es mi opinión.


    Jose R. MCP
    Code Samples

    martes, 27 de marzo de 2018 18:59
  • Otra solución más "ortodoxa", es el uso de secuencias.  Como sería una secuencia por ID_Grupo, habría que crearlas dinámicamente.  Ya una vez creadas "tenemos" la atomicidad del IDENTITY en nuestras manos.

    ¿Les parece mejor?  A mí no particularmente.  Cambio la posibilidad de borrar registros por SQL dinámico.  Pero bueno, yo no soy el del problema.  Tal vez el del problema deba pronunciarse y decir por dónde le gustaría más ir.


    Jose R. MCP
    Code Samples

    martes, 27 de marzo de 2018 19:03
  • Cuando publiqué esta pregunta, realmente no me imaginé que se convertiría en todo un debate y es bueno que ocurra, así se sigue enriqueciendo este espacio y quienes recién comenzamos nos damos cuenta de que no todo está 100% afinado aún o mejor dicho, que hay múltiples opciones de las cuales conocer y evaluar para resolver un problema.

    Efectivamente como lo comentan, la idea de manejar un folio consecutivo muy a parte de la PK es para que el usuario pudiera tener una referencia de su movimiento, mero control interno. Para mi caso en particular el movimiento no se podrá eliminar sino solo cancelar, más sin embargo no me es viable utilizar la vista que se propuso porque hay registros que debo importar a la tabla provenientes de otra BD, mismos que ya deben traer su folio propio y al volverlo a asignar perdería referencia. 

    Hasta el momento la idea de generarlo y guardarlo en tiempo de inserción me ha parecido la mejor propuesta. Agradezco muchísimo su ayuda.


    • Editado Zarpis martes, 27 de marzo de 2018 19:24
    martes, 27 de marzo de 2018 19:23
  • Jose,

    Talvez porque tengo otra idea pero yo hablaría con el cliente, una que use para solventar esto aunque tampoco me gusta es que dábamos un id temporal y luego recalculabamos diariamente los valores y dábamos uno definitivo al día siguiente, lo cual hacia que hubiera una discrepancia de un día, para solventar eso tomábamos una vista que uniera la table temporal diaria con el archivo correcto.

    Pero bueno soluciones creativas hay muchas.


    Blog: www.sqlservertoolbox.blogspot.com.mx

    martes, 27 de marzo de 2018 19:30
  • Si es simplemente por los valores de folio importados, puede simplemente agregar una constante al ROW_NUMBER para que los valores de folio generados inicien más allá del valor más alto importado.  Puede hacer una pequeña tabla que indique esta constante para cada ID_Codigo que tenga.  Los que no estén listados se asume constante cero.  La modificación es sencilla de hacer.

    Ejemplo:

    ID_Codigo 1 tiene un ID de folio máximo de 1112 y existen 1002 registros.  La vista (o CTE) entonces calcularía que el siguiente número de folio sería el 1003, pero está fallando porque debería ser 1113.  Entonces la constante para este ID_Codigo sería de 10.

    Entonces necesitamos una tabla muy sencilla:

    Create Table dbo.tblDesfases
    (
        ID_Codigo int Not Null Primary Key Clustered
        , Valor int Not Null Check (Valor >= 0)
    );


    La vista se modifica así:

    Create View dbo.vwTabla
    As
    Select
        t.PK
        , t.ID_Grupo
        , Row_Number() Over (Partition By t.ID_Grupo Order By t.PK Asc) + Coalesce(d.Valor, 0) As Folio
    From
        dbo.tabla As t
        Left Join
        dbo.tblDesfases As d
        On
        t.ID_Codigo = d.ID_Codigo
    ;
    Go

    Y la versión con CTE sería:

    Create Procedure dbo.uspInsertarNuevo
        ... --Parámetros para los valores de las distintas columnas, incluido uno llamado @idGrupo para ID_Grupo.
        , @id int output --o bigint o lo que se necesite para devolver el PK del nuevo registro
    As
    Begin
        Set NoCount On;
    
        Declare @errorNumber int = 0;
    
        Begin Tran; --Meramente para hacer atómicas las modificaciones, no para evitar problemas de concurrencia.
    
        Begin Try
            Insert Into dbo.tabla (....)
            Values (....@idGrupo, ....);
    
            Set @id = Scope_Identity();
    
            With calculoFolios As
            (
                Select
                    t.PK
                    , Row_Number() Over (Partition By t.ID_Grupo Order By t.PK Asc) + Coalesce(d.Valor, 0) As [FolioCalculado]
                From
                    dbo.tabla As t
                    Left Join
                    dbo.tblDesfases As d
                    On t.ID_Codigo = d.ID_Codigo
            )
            Update
                dbo.Tabla
            Set
                Folio = cf.FolioCalculado
            From
                dbo.Tabla As t
                Inner Join
                calculoFolios As cf
                On t.PK = cf.PK
            Where
                t.PK = @id
            ;
            Commit Tran;
        End Try
        Begin Catch
            Set @errorNumber = Error_Number();
            Rollback Tran;
        End Catch
        Return @errorNumber;
    End
    Go

    Ah, y si le preocupa el llenado de la tabla de desfases, debería ser muy sencillo de llenar en una sola sentencia (después de importar los datos):

    With agrupaciones As
    (
        Select
            ID_Grupo
            , Max(Folio) As [MaxFolio]
            , Count(Folio) As [TotalFolios] --O tal vez Count(*).  Depende de sus datos.
        From
            dbo.Tabla
        Group By
            ID_Grupo
    )
    Insert Into dbo.tblDesfases
    Select
        ID_Grupo
        , MaxFolio - TotalFolios
    From
        agrupaciones
    Where --Opcional.  Simplemente para no escribir ceros.
        MaxFolio <> TotalFolios
    ;
    Ah, y le agregué un CHECK a la tablita de control para asegurarnos de que no hay constantes negativas.


    Jose R. MCP
    Code Samples


    • Editado webJose martes, 27 de marzo de 2018 19:42
    • Marcado como respuesta Zarpis miércoles, 28 de marzo de 2018 18:03
    martes, 27 de marzo de 2018 19:36
  • A ver, puedo entender que lo veas como la solución viable, pero entre usar un método que es un procedimiento almacenado (que garantiza la zona crítica) una vista que no puede garantizar la integridad referencial + un trigger además instead of por que en vistas no se pueden poner otros.... creo que es complicarse mucho las existencia, amén que no sería el primer dba que te prohíbe crear triggers (a veces hasta estoy de acuerdo) en sus bases de datos. 

    No obstante, las alternativas están claras y para Nuestro amigo zarpis ... creo que han quedado claras, al final es el objetivo. 


    Comparte lo que sepas, aprende lo que no sepas (FGG)
    portalSQL
    El rincón del DBA

    miércoles, 28 de marzo de 2018 6:39
    Moderador
  • Comprendo bien sus puntos.  Yo solamente defiendo el hecho de que la solución es buena; satisface todos los objetivos.  Para gustos, colores.  A usted le parece compleja.  A mí me parece muy sencilla.  He hecho cosas en SQL con SQL dinámico unas 30 veces más complicadas (por ponerle un número).  Trabajé un par de años como administrador mundial de Master Data Services, una versión adulterada por la compañía.  Escribir T-SQL de 3000 líneas era mi pan de cada día, así que una vista, un PA y un trigger que tal vez no suman 150 líneas lo veo super sencillo.

    Cabe notar que la tabla de desfases que propuse al final sirve para resolver el problema de borrado de registros, aunque ya el originador de la pregunta aseguró que tal escenario nunca pasaría.  Pero en fin, para quien le sirva:  Ya no ocupamos un INSTEAD OF DELETE trigger en la tabla (nunca dije que en la vista).  Ahora puede usarse un AFTER DELETE trigger que simplemente incrementa en 1 el valor de desfase por cada registro borrado.  Eso mantiene el número de folio tal como si fuera un campo IDENTITY.

    También le recuerdo al originador de la pregunta que es muy probable que pueda realizarse una solución con secuencias.  Requiere de SQL dinámico para crear y evaluar las secuencias, eso sí.  En mi opinión, SQL dinámico es más complicado que una vista, un PA y un trigger.  ¡Y nótese que el trigger es opcional!  Solamente si necesitáramos ya sea un seguro contra borrones o permitir correctamente el borrado.

    Al final esta pregunta me enseñó un par de cosas que aparentemente habían escapado por años de mi radar.

    Ah y un detalle más:  Comprobé lo que me decía que lectura no bloquea lectura ni en SERIALIZABLE.  Tiene usted razón.  Pero entonces ya divisé la manera correcta de usar SERIALIZABLE (ya la verifiqué).


    Jose R. MCP
    Code Samples

    miércoles, 28 de marzo de 2018 7:51
  • Como bien dices Jose, yo también he hecho cosas mil veces mas complejas, pero en mi mente siempre está el mantener la solución cuanto más simple... mejor. 

    Sobre lo de serializable, ya te lo dije, es posible que se pueda hacer algo con el hint updlock pero volviendo al hilo inicial, incluso el select max con el control de errores me parece más eficaz que consumir recursos en cada query.

    Si has trabajado como dba sabrás que lo normal es que el número de lecturas sea el 95% mientras que el de escrituras sea el 5%, así pues, llevar la parte costosa de la consulta a la selección, es decir en una vista me parece una mala idea. Como tu bien dices para gustos los colores, pero esto no es cuestión de gustos sino de tecnología, y aunque tu solución podría valer, que vale, yo no la implementaría por los motivos que te he expuesto. y que son

    1.- Es más sencillo de otra forma, (incluso me gusta más la opción de select max mas control de errores )

    2.- la complejidad queda del lado de las escrituras no de las lecturas (que siempre serán mas y más frecuentes) por tanto el overload global de mi BBDD es menor.

    3.-(para mi la más importante) admite integridad referencial sin subterfugios de programación. Y no es sensible a borrados incluso sin triggers

    4. no necesito triggers y no es un tema menor.

    Sobre las secuencias, claro que si, son otra herramienta a usar, pero tendrás que declarar tantas secuencias como grupos... no veo que simplifique el problema, bien al contrario. 

    En resumen, tu solución, si bien puede funcionar, necesita de conocimientos de SQL no de novato, sino de senior, Además tiene un orden de complejidad que para gente con tu experiencia parece trivial, pero que no lo es.  Es consumidora de recursos particularmente en selects ( y si, se que con los índices adecuados el rownumber irá genial.... pero .. es otra pieza mas y otro conocimiento mas)  y sobre todo no aporta absolutamente nada al select max, ni una sola ventaja, por eso es que te la critico no por que técnicamente no sea viable.

    Entiendo también que no te guste mi aproximación. A mucha gente no le gusta, a mi lo que me atrae es que es una forma de implementar zona crítica.

    Se puede hacer de otra forma con sp_getapplock, así que  si a alguien no le gusta la idea de generar una tabla adicional  puede mantener el select max pero usando antes de leerlo sp_getapplock para obtener un bloqueo exclusivo  sobre un nombre, y asegurarse así esa misma zona crítica.


    Comparte lo que sepas, aprende lo que no sepas (FGG)
    portalSQL
    El rincón del DBA

    miércoles, 28 de marzo de 2018 8:14
    Moderador
  • Bueno, ahora que sé cómo usar SERIALIZABLE apropiadamente, el uso del MAX() parece la opción más sencilla.  Mi solución no requiere de control de errores, pienso yo, y es mucho más mundana que un hint updlock.  Es una analogía exacta a programación multi hilo en Windows (cualquier lenguaje).  Simplemente defino una tabla de "mutexes" que no son más que un ID IDENTITY y un campo numérico con un valor arbitrario.  Cualquiera sirve.  Hasta tipo bit puede ser.  Luego simplemente el PA hace un update del "mutex" asignado para el recurso a bloquear (idéntico a programación Windows), que se identifica con el campo ID.  El valor que se asigna es irrelevante.  Cualquiera sirve.  De esta forma el bloqueo que se impone es el que yo esperaba:  Cualquier otra conexión corriendo el mismo PA se bloqueará en esta primera sentencia UPDATE hasta que la conexión que lo está usando haga COMMIT o ROLLBACK.  Exacto a un mutex o critical section en Windows.  Como ve usted, no soy DBA. :-)  Definitivamente.

    Con el uso del "mutex", un PA con una transacción SERIALIZABLE podrá usar MAX() y todos los cálculos que quiera tranquilamente sin preocuparse por la concurrencia.  Por eso pienso que no necesita de control de errores.

    Secuencias:  De acuerdo también.  Me parece más complejo.  Sin embargo satisfacería a quienes dicen andar buscando una solución similar al IDENTITY.

    Si la vista no gusta, puede hacerse todo dentro del PA.  Insisto que no es problemático.  Si resultara lento, es claro que lo que se ocupa es indizar ID_Codigo.  Tal índice ayudará a otras consultas del sistema, estoy seguro.  El PA sería para escritura, así que es como usted dice que le gusta:  La complejidad del lado de escritura.

    Por eso digo yo que me parece más cuestión de gustos:  Creo que satisfago la mayoría de sus puntos.  Creo que solamente fallo en la parte de "subterfugios", pero también esos están sujetos a opinión.  A usted le parece un subterfugio el uso de ROW_NUMBER; a otros podría parecerles un subterfugio el uso de una tabla auxiliar (que tanto usted como yo usamos).


    Jose R. MCP
    Code Samples

    miércoles, 28 de marzo de 2018 8:56
  • Me encanta la solución Jose, simplemente me parece que en esencia es exactamente igual que la que yo proponía :)  Una tabla auxliar que sirve de mutex para hacer una zona crítica.. 

    Se puede sin esa tabla hacer esa zona critica con sp_Getapplock, pero vamos en esencia 100% de acuerdo, esa solución me gusta.

    Buen debate webJose


    Comparte lo que sepas, aprende lo que no sepas (FGG)
    portalSQL
    El rincón del DBA

    miércoles, 28 de marzo de 2018 10:42
    Moderador