Finding missing gaps of data in a table with ~2.5 Million rows

Question

I work for an economics consulting company and all of our data is housed in SQL Server 11 (2012). Our data essentially consists of a date column, a raw data column, several calculations columns and a column that has a shortcode to distinguish each set of data from the next.

We have thousands of these sets that are all loaded into the same table (~2.5 Mil rows total) and then drawn from and ordered with a query. They are generally ordered by date, starting with the first month and moving month by month until the last month. The start and end dates of each series varies from just a year's worth of time to 100+ years.

Recently, we've been having some issues where random sections of data just disappear. The entire row just up and leaves, which makes finding these missing rows somewhat of a challenge without combing through every single month to check if a month is missing, a somewhat impossible task for a 2.5 million row table.

My boss has tasked me with writing a query/stored procedure that will look through this giant table and look to see which sets having missing rows and where they are.

I've been trying to work through this problem that is a little bit above where my SQL skills are and I can't seem to find anybody that has had a similar issue anywhere on the web. I'm going to go through what I already have and maybe someone can at least tell me if I'm headed in the right direction and possibly provide some insight as to where I should go from here.

The best solution I could find on the web was using a CTE to create a temp table of dates and then compare them with the original table. This works great if I was just scanning for problems in one particular data set, but I have many data sets within the same table, all with different starting and end dates. So I went with it anyways in hopes that I could eventually expand it to search through the lot of them. Here's my code:

declare @startDate Date, @endDate Date 
set @startDate = '2000-01-01'
set @endDate = '2016-11-01'

;with GetDates As  
(  
select @startDate as TheDate
UNION ALL  
select DATEADD(MONTH,1, TheDate) from GetDates
where DATEADD(MONTH,1, TheDate) <= @endDate
)

SELECT TheDate,SHORTCODE,MonthYear 
From GetDates
LEFT OUTER JOIN VWTBL_INDICATOR
ON GetDates.TheDate=VWTBL_INDICATOR.MonthYear
AND VWTBL_INDICATOR.SHORTCODE='RMI WEST'
OPTION(MAXRECURSION 1000)

'RMI West' would be the shortcode that marks this particular data set and it is missing data from November of 2004 to March of 2005 which appear as nulls when I execute this query. That is almost exactly what I need, but for every data set that I have in the table.

How do I write this query properly? Our company contracts out a bunch of the work done on our database so I'm not really all that familiar with the guts of it. We have an upload function and when I pull the data in using that, everything is fine. But when I look at the data a week or two later, it is missing random dates. They have been looking into a solution, but we need a way to find these gaps in the meantime.

We will always be checking months. Our data almost always starts on January in a given year and ends on the current month or close to it if the data hasn't come out yet.

I have a metadata table for the shortcodes, but the expected number of months that each shortcode has is not included as we haven't had any use for this information until now. I could inquire about adding in the fields and modifying the update script to include them when the data is updated, if this would be essential in getting this to work properly.

The company I work for has historically stored all of its data on an overwhelming number of Excel spreadsheets. They have been in the process of transitioning all of this data into SQL. My job is to check it through various methods to make sure that the data on SQL Server matches up with the data in the original spreadsheet. The raw data is accessible through the spreadsheet network on our file system. When updating data, we still use Excel to update the information and then load it in SQL using the upload function that I mentioned previously. The data has been mostly clean and matching for some time now. Only within the last couple months has this data gap issue arisen.

Answer 1

There is no need to generate dates.

---

The following query will give you a list of SHORTCODES with no rows at all:

select SHORTCODE from shortcodes
except
select SHORTCODE from VWTBL_INDICATOR

---

The following query will give you the continuous ranges of MonthYear per 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

If you wish you can use the following version which has an additional layer of information:

• missing_from_MonthYear + to_MonthYear: missing range in the middle
• ranges: Number of ranges per SHORTCODE (ranges>1 means you have gaps in the middle)
• range_seq: the sequential number of each SHORTCODE range
• is_first: Indication for the first range per SHORTCODE (check from_MonthYear to see if you are missing preceding dates)
• is_last: Indication for the last range per SHORTCODE (check to_MonthYear to see if you are missing following dates)

---

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

Answer 2

To start, while with only 202 months to check it won't be a huge issue, a recursive CTE is generally the worst possible way to derive a set, in terms of performance (I prove this here and here).

If you're going to be running this query more than once (and it sounds like you will be, until you solve the separate issue of who/what is deleting this data and creating the gaps in the first place), why not just build a months table that will always be there?

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 years of months, which will work through the year 2029, stored in a whopping 72kb. When I first wrote this I sarcastically emphasized whopping, but I should explain why this has 9 pages instead of the expected 2. In current versions of SQL Server (I initially tested this on SQL Server 2016, but the same is true in v.Next), the storage engine reserves an entire, uniform extent for new objects. This is 8 x 8kb pages, plus the IAM page for 72kb - in this case only one of the data pages is actually required, so 7 remain unallocated. This means they won't show up in all catalog views, but they're still easy to find (click to enlarge):

You can turn this behavior off for user databases, but personally I wouldn't (they made it the default for a reason). Your first instinct might be about saving memory rather than disk space, but while this puts 72kb on disk, only 16kb will ever be loaded into the buffer pool. So no need to panic about that.

Now your query can be:

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;

Note that currently this will identify all months in your defined range where a ShortCode doesn't appear, even if it's outside the range that is valid for that ShortCode. If those valid ranges per ShortCode are defined somewhere, please add that information to the question.

What on earth is a "VWTBL"?

Answer 3

I'm going to address the question you didn't ask: why is my data disappearing?

Data can't just disappear from SQL tables on its own (without corruption), there must be something deleting it.

It could be a malicious user or something, but in my experience it is much more likely to be something like a poorly written archive routine that is catching more rows than intended. Are there maintenance routines that run on the database to clean up old records?

You mentioned you contract out some of the database support, can you raise this as a high-priority issue with them? Could be one of their routines doing it.

Also, these rows might not be deleted how you think: maybe there is a badly written query that UPDATES a bunch of rows with the wrong date, and a different routine that flags them as invalid/duplicate and DELETES them or something.

Finally, is this table partitioned? If it is partitioned by date, and you do some fancy rolling date windows there could be issues with how exactly that is set up.

But from scratch, here is what I would check:

1. Check the Database for Corruption

If you aren't doing it routinely, do a DBCC CHECKDB on the database during off hours. If it returns an error, you may have a bigger problem.

2. Lock down your user security

Identify the types of access that different groups of people need, and give them the bare minimum necessary. You can do this on the database level (via roles), or at the individual table level (via explicit permissions).

Only running reports? Read only.

Doing data imports? INSERT, but not UPDATE or DELETE.

3. Run a trace to watch database activity

You can run a Profiler Trace (or start a server-side trace) to see when the deletes occur. Add a filter for DELETE to reduce the number of rows captured.

4. Track deletes on the table

There are a few ways to track any delete statements that occur, discussed in this question. In your situation, sounds like a table trigger would be the simplest solution.

Answer 4

You could use a calendar table and a subquery to generate the date span of your shortcodes.

The following example uses snippets from Creating a date dimension or calendar table in SQL Server - Aaron Bertrand.

-- if regional settings are interfering with interpretation of dates/literals:
/*
set datefirst 7;
set dateformat mdy;
set language us_english;
--*/

if object_id('dbo.Calendar_Months') is not null drop table dbo.Calendar_Months;
create table dbo.Calendar_Months (
    [Date]         date     not null
  , [Year]         smallint not null
  , [Month]        tinyint  not null
  , [Quarter]      tinyint  not null
  , [YearMonth]    char(7)  not null /* yyyy-mm */
  , [YearQuarter]  char(7)  not null /* yyyy-qq */

  , constraint pk_Calendar_Months primary key clustered (date)
  );

declare @FromDate date = '19000101';
declare @ThruDate date = '20201201';

with n as (select n from (values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) t(n))
, d as (
  select DateValue=convert(date,dateadd(month
      , row_number() over (order by (select 1)) -1, @fromdate))
    from         n as deka
      cross join n as hecto
      cross join n as kilo    
      cross join n as [10k]   
      --cross join n as [100k]
      --cross join n as mega
)
insert into dbo.Calendar_Months 
    ([Date], [Year], [Month], [Quarter], [YearMonth],[YearQuarter])
  select top (datediff(month, @FromDate, @ThruDate)+1) 
      [Date]        = DateValue
    , [Year]        = convert(smallint,datepart(year,DateValue))
    , [Month]       = convert(tinyint,datepart(month,DateValue)) 
    , [Quarter]     = convert(tinyint,datepart(quarter,DateValue)) 
    , [YearMonth]   = convert(char(7) ,convert(char(4), datepart(year,DateValue))
                    +right('0'+convert(varchar(2), datepart(month,DateValue)),2))
    , [YearQuarter] = convert(char(7) ,convert(char(4), datepart(year,DateValue))
                    +'-Q'+convert(char(1), datepart(quarter,DateValue)))

    from d
    order by DateValue;

---

Then you could do something like this:

  with ShortCodeDateSpan as (
    select 
          i.shortcode
        , FromDate=min(i.monthyear)
        , ThruDate=max(i.monthyear)
      from vwtbl_indicator as i
      group by i.shortcode
    )

    select 
          scds.shortcode
        , MissingYearMonth=cm.Date
      from ShortCodeDateSpan as scds
        inner join dbo.Calendar_Months cm 
             on cm.Date >= scds.FromDate
            and cm.Date <= scds.ThruDate
        where not exists (
          select 1 
            from vwtbl_indicator as i 
            where i.shortcode=scds.shortcode 
              and cm.Date = i.MonthYear
          );

calendar and numbers tables reference:

• Generate a set or sequence without loops - Aaron Bertrand
• Creating a Date Table/Dimension in SQL Server 2008 - David Stein
• Calendar Tables - Why You Need One - David Stein
• Creating a date dimension or calendar table in SQL Server - Aaron Bertrand
• TSQL Function to Determine Holidays in SQL Server - Tim Cullen
• F_TABLE_DATE - Michael Valentine Jones