6

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.

2

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
9

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):

enter image description here

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"?

  • 1
    My guess would be "View Table", VWTBL_IINDICATOR is the name of the table I was referring to with 2.5 Mil rows... I didn't create this database so I'm not sure of the thought process that went into name it. – Zimmerel Jan 3 '17 at 18:12
  • 5
    @Zimmerel Sounds like a doorhouse. I have a house, and you use a door to get in. So, doorhouse! – Aaron Bertrand Jan 3 '17 at 18:20
  • @AaronBertrand I'd be interested to know why you chose a left outer join in this instance over not exists. -- Should I use not in, outer apply, left outer join, except, or not exists? - Aaron Bertrand (You are messing with my gospel.) – SqlZim Jan 3 '17 at 19:08
  • 1
    Interesting solution, I will give it a shot and return with my results. Also, I appreciate keeping the code similar to the question. I am not the most SQL-savvy person and could stand to learn a whole lot more. I went to school for programming and took a SQL class and then some coverage in my web design class. Perhaps you could explain why not exists would be a better option? Or at least a link to some reading would be helpful. Thanks! – Zimmerel Jan 3 '17 at 19:32
  • 1
    @SqlZim Well, depending on your version of SQL Server, and perhaps on your database-level setting in SQL Server 2016 (you can switch between mixed extents and uniform extents). In modern versions you will get a full extent for the data pages (8 x 8kb), with only one page allocated, and the rest reserved. Plus the IAM page, which comes from a mixed extent. Here is the test I did. Note that this is 9 pages / 72kb on disk, but only the two allocated pages will ever be loaded into the buffer pool. I've updated the answer to include this information. – Aaron Bertrand Mar 4 '17 at 3:26
4

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.

  • I realize that data wouldn't just disappear, but I guess the wording in my question may have made it out to appear that way. I have brought it up to database support on numerous occasions and they respond saying they will look into it, but haven't seen any results in regards to that as of yet. The table is not partitioned, but I do believe there are maintenance routines, something I had suspicions of being the cause of this issue from the beginning. Thank you for outlining the troubleshooting steps above, I will look into them when time permits. – Zimmerel Jan 3 '17 at 20:04
1

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:

  • 1
    MIN and MAX is clever, but if the gap is at the end, it wouldn't be spotted. The bounds should be the start and end date, for all shortcodes, not the observed min and max in the data for any given shortcode. Or if the "valid range" for a given shortcode is known that information should be added to the question (I don't see it). – Aaron Bertrand Jan 3 '17 at 18:50
  • Very true. If those values are to be found somewhere, I hope it would be simple enough to modify the example query above to use them instead of the min() max() cte/subquery. – SqlZim Jan 3 '17 at 18:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.