Solución:
Para empezar, si bien con solo 202 meses para verificar no será un gran problema, un CTE recursivo es generalmente la peor forma posible de derivar un conjunto, en términos de rendimiento (lo demuestro aquí y aquí).
Si va a ejecutar esta consulta más de una vez (y parece que lo hará, hasta que resuelva el problema separado de quién / qué está eliminando estos datos y creando las brechas en primer lugar), ¿por qué no simplemente compilar una mesa de meses que siempre estará ahí?
CREATE TABLE dbo.Months([Month] date PRIMARY KEY);
DECLARE @StartDate date="20000101",
@NumberOfYears int = 30;
INSERT dbo.Months([Month])
SELECT TOP (12*@NumberOfYears)
DATEADD(MONTH, ROW_NUMBER() OVER (ORDER BY number) -1, @StartDate)
FROM master.dbo.spt_values;
30 años de meses, que funcionarán hasta el año 2029, almacenados en un grandísimo 72kb. Cuando escribí esto por primera vez, enfaticé sarcásticamente la friolera, pero debería explicar por qué tiene 9 páginas en lugar de las 2 esperadas. En las versiones actuales de SQL Server (inicialmente probé esto en SQL Server 2016, pero lo mismo es cierto en v.Next ), el motor de almacenamiento reserva una extensión completa y uniforme para nuevos objetos. Se trata de 8 páginas de 8 kb, más la página de IAM por 72 kb; en este caso, solo se requiere una de las páginas de datos, por lo que 7 permanecen sin asignar. Esto significa que no aparecerán en todas las vistas del catálogo, pero aún así son fáciles de encontrar (haga clic para ampliar):
Puede desactivar este comportamiento para las bases de datos de los usuarios, pero yo personalmente no lo haría (lo convirtieron en el predeterminado por una razón). Su primer instinto podría ser ahorrar memoria en lugar de espacio en disco, pero mientras esto pone 72kb en disco, solo se cargarán 16 kb en el grupo de búferes. Así que no hay necesidad de entrar en pánico por eso.
Ahora tu consulta puede ser:
DECLARE @startDate date="20000101", @endDate date="20161101";
;WITH shortcodes AS
(
SELECT DISTINCT ShortCode
FROM dbo.VWTBL_INDICATOR
WHERE MonthYear >= @startDate AND MonthYear <= @endDate
)
SELECT m.[Month], s.ShortCode
FROM dbo.Months AS m
CROSS JOIN shortcodes AS s
LEFT OUTER JOIN dbo.VWTBL_INDICATOR AS vwtbl
ON s.ShortCode = vwtbl.ShortCode
AND m.[Month] = vwtbl.MonthYear
WHERE m.[Month] >= @startDate AND m.[Month] <= @endDate
AND vwtbl.MonthYear IS NULL;
Tenga en cuenta que actualmente esto identificará todos los meses en su rango definido donde no aparece un ShortCode, incluso si está fuera del rango válido para ese Código corto. Si esos rangos válidos por ShortCode están definidos en algún lugar, agregue esa información a la pregunta.
¿Qué diablos es un “VWTBL”?
Voy a abordar la pregunta que no hizo: ¿Por qué mis datos están desapareciendo??
Datos hipocresía simplemente desaparece de las tablas SQL por sí solo (sin corrupción), debe haber algo que lo elimine.
Podría ser un usuario malintencionado o algo así, pero en mi experiencia es mucho más probable que sea algo así como una rutina de archivo mal escrita que captura más filas de las previstas. ¿Existen rutinas de mantenimiento que se ejecutan en la base de datos para limpiar registros antiguos?
Mencionaste que contrataste parte del soporte de la base de datos, ¿puedes plantear esto como un problema de alta prioridad con ellos? Podría ser una de sus rutinas hacerlo.
Además, es posible que estas filas no se eliminen como usted piensa: tal vez hay una consulta mal escrita que ACTUALIZA un montón de filas con la fecha incorrecta y una rutina diferente que las marca como inválidas / duplicadas y las BORRA o algo así.
Finalmente, ¿esta tabla está particionada? Si está particionado por fecha, y tiene algunas ventanas de fecha móviles elegantes, podría haber problemas con la forma exacta en que se configura.
Pero desde cero, esto es lo que comprobaría:
1. Verifique la base de datos en busca de corrupción
Si no lo está haciendo de forma rutinaria, haga un DBCC CHECKDB en la base de datos durante las horas libres. Si devuelve un error, es posible que tenga un problema mayor.
2. Bloquea la seguridad de tu usuario
Identifique los tipos de acceso que necesitan los diferentes grupos de personas y bríndeles el mínimo necesario. Puede hacer esto en el nivel de la base de datos (a través de roles) o en el nivel de la tabla individual (a través de permisos explícitos).
¿Solo ejecuta informes? Solo lectura.
¿Haciendo importaciones de datos? INSERT, pero no ACTUALIZAR ni ELIMINAR.
3. Ejecute un seguimiento para observar la actividad de la base de datos.
Puede ejecutar un Profiler Trace (o iniciar un rastreo del lado del servidor) para ver cuándo ocurren las eliminaciones. Agregue un filtro para ELIMINAR para reducir el número de filas capturadas.
4. Realice un seguimiento de las eliminaciones en la mesa
Hay algunas formas de realizar un seguimiento de las declaraciones de eliminación que se produzcan, que se analizan en esta pregunta. En su situación, parece que un disparador de mesa sería la solución más simple.
No es necesario generar fechas.
La siguiente consulta le dará una lista de SHORTCODES sin filas en absoluto:
select SHORTCODE from shortcodes
except
select SHORTCODE from VWTBL_INDICATOR
La siguiente consulta le dará los rangos continuos de MonthYear por SHORTCODE.
select SHORTCODE
,min(MonthYear) as from_MonthYear
,max(MonthYear) as to_MonthYear
,count(*) as months
from (SELECT SHORTCODE
,MonthYear
,row_number() over (partition by SHORTCODE order by MonthYear) as rn
From VWTBL_INDICATOR
) t
group by SHORTCODE
,DATEADD(month,-rn,MonthYear)
order by SHORTCODE
,from_MonthYear
Si lo desea, puede utilizar la siguiente versión que tiene una capa adicional de información:
- missing_from_MonthYear + to_MonthYear: rango faltante en el medio
- rangos: número de rangos por SHORTCODE (rangos> 1 significa que tiene espacios en el medio)
- range_seq: el número secuencial de cada rango de SHORTCODE
- is_first: Indicación para el primer rango por SHORTCODE (verifique from_MonthYear para ver si faltan fechas anteriores)
- is_last: Indicación para el último rango por SHORTCODE (verifique to_MonthYear para ver si faltan las siguientes fechas)
select SHORTCODE
,from_MonthYear as exists_from_MonthYear
,to_MonthYear as exists_to_MonthYear
,dateadd (day,1,to_MonthYear) as missing_from_MonthYear
,dateadd (day,-1,lead (from_MonthYear) over (partition by SHORTCODE order by from_MonthYear)) as missing_to_MonthYear
,count (*) over (partition by SHORTCODE) as ranges
,row_number () over (partition by SHORTCODE order by from_MonthYear) as range_seq
,case from_MonthYear when min(from_MonthYear) over (partition by SHORTCODE) then 1 end as is_first
,case to_MonthYear when max(to_MonthYear) over (partition by SHORTCODE) then 1 end as is_last
from (select SHORTCODE
,min(MonthYear) as from_MonthYear
,max(MonthYear) as to_MonthYear
,count(*) as months
from (SELECT SHORTCODE
,MonthYear
,row_number() over (partition by SHORTCODE order by MonthYear) as rn
From VWTBL_INDICATOR
) t
group by SHORTCODE
,DATEADD(month,-rn,MonthYear)
) t
order by SHORTCODE
,from_MonthYear