Diario de SQL Server

ctes

Eliminar muchos registros mientras la tabla sigue disponible

Escrito por qwalgrande 13-11-2009 en General. Comentarios (0)

Es una práctica habitual, más propia de principios de año que de finales, tener que pasar datos a histórico. El volcado no suele ser problemático, pero eliminar un volumen importante de registros sí, y más cuanto mayor sea el tamaño de la tabla a trasvasar.

 

Cabe decir que en este tipo de operaciones la disponibilidad que deba tener la tabla mientras se vacía es lo más importante. Si podemos hacerlo a nuestras anchas, con tiempo de mantenimiento, la cosa es mucho más sencilla, podemos hacerlo de cien formas. Así que me centraré en la parte complicada, esto es, cómo eliminar la hueva de registros, gigas y gigas, mientras los accesos a la tabla continuan como si nada.

 

Aunque hay muchos otros métodos, expongo aquí uno que vengo usando últimamente. Lo primero es preparar una tabla que tenga únicamente la clave primaria de la tabla objeto del borrado y que contenga los valores que tenemos que borrar, cargándola mediante un método lo más ligero posible, es decir, no con un select..into, que es de lo más agresivo, sino por ejemplo usando el asistente de exportación de datos del Management Studio, con una consulta tal que "select IdTabla from Tabla with(nolock) where fecha < '20050101'". Llamaré a esta tabla, "TablaRef".

 

El borrado se haría de la siguiente script:

 

while exists(select top 1 IdTabla from TablaRef with(nolock)

  begin

    begin tran

    ;with Cte (IdTabla) as (select top 1000 IdTabla from TablaRef order by IdTabla)

    delete from Tabla where IdTabla in (select IdTabla from Cte)

    ;with Cte (IdTabla) as (select top 1000 IdTabla from TablaRef order by IdTabla)

    delete from TablaRef where IdTabla in (select IdTabla from Cte)

    commit

  end


Desde luego, hay otros métodos, pero este permite hacer un seguimiento de lo que queda por borrar y al ir eliminando registros de 1000 en 1000, evitas tener mucho tiempo bloquedos los registros que no se van a eliminar. Además, buscamos los registros siempre por la clave primaria, con lo que el rendimiento del borrado será, por lo general, bastante bueno.

 

Row_Number, recuento secuencial de filas y recuentos parciales

Escrito por qwalgrande 31-10-2009 en General. Comentarios (1)

Otro par de scripts prácticos, en este caso del uso de row_number. Esta función, añadida con SQL Server 2005, permite realizar recuentos globales y, algo que me parece más interesante, recuentos parciales, aplicándole la cláusula "partition". Lo primero, la teoría:

 

http://msdn.microsoft.com/es-es/library/ms186734.aspx

 

Y un par de casos prácticos. Por ejemplo, un caso rebuscado, tenemos la tabla de facturas y la tabla de líneas de factura. Queremos representar junto a cada factura, el número que ocupa cada línea dentro de esa factura:

select Num_Factura, F.IdCliente,

Pos_Linea = row_number() over (partition F.IdFactura order by LF.IdLinFactura),

LF.IdProducto, LF.Importe

from Facturas F inner join LineasFactura LF on F.IdFactura = LF.IdFactura

where F.IdCliente = 10025656

 

Otro más, tenemos que mostrar el detalle ordenado de una incidencia, con todo su desglose, siendo muy importante que se identifique el primero de los pasos y el último:

 

;with V (Id_detalle_Incidencia, ID_SECUENCIA, ID_ULTIMO) as

(select Id_detalle_Incidencia, Id_Incidencia

ID_SECUENCIA = row_number() over (partition by Id_Incidencia order by Id_detalle_Incidencia),

ID_ULTIMO = row_number() over (partition by Id_Incidencia order by Id_detalle_Incidencia desc)

from Detalle_Incidencias D

where fecha >= '20091031' and fecha < '20091101'

)

select Id_Incidencia, Id_Detalle_Incidencia, ID_SECUENCIA,

Indicador_Detalle_Inicial = case when ID_SECUENCIA = 1 then 'S' else 'No' end,

Indicador_Detalle_Final = case when ID_ULTIMO = 1 then 'S' else 'No' end

from V

 

Así, se observa que con una CTE, se puede obtener el secuencial creciente y decreciente para su uso posterior a conveniencia.