4

Temporal tables and time periods are nothing special in the IT world. But somehow it seems my request is rather unique because I cannot find anything useful for it at all.

What I have are two tables, one containing static data and the other one dynamic data for entries of the first table which changes over time. For each row in the second table there are two columns ValidFrom and ValidUntil whereas the latter can be null if there is no planned end of validity.

Simplified, the schema looks like this:

           tbStatic
+----+------------+------------+
| Id | Attribute1 | Attribute2 |
+----+------------+------------+
|  1 | foo        | bar        |
|  2 | baz        | foo        |
+----+------------+------------+

                                       tbDynamic
+----+------------+---------------------+---------------------+------------+------------+
| Id | tbStaticId |      ValidFrom      |     ValidUntil      | Attribute1 | Attribute2 |
+----+------------+---------------------+---------------------+------------+------------+
|  1 |          1 | 2018-01-01 00:00:00 | 2018-01-31 23:59:59 |          1 |          0 |
|  2 |          2 | 2018-04-01 00:00:00 | 2018-04-02 11:59.59 |          2 |          1 |
|  3 |          1 | 2018-02-01 00:00:00 | null                |          2 |          1 |
|  4 |          2 | 2018-05-01 00:00:00 | 2018-06-01 00:00:00 |         23 |         15 |
|  5 |          2 | 2018-07-01 01:23:45 | 2018-07-05 23:12:01 |         80 |         12 |
+----+------------+---------------------+---------------------+------------+------------+

As you might have spotted, there is the possibility that we have holes between time periods. What we cannot have is overlapping periods, though. This means it is impossible to have overlapping time periods for the same tbStaticId.

Unfortunately, this is only a requirement until now and although it is enforced in the application using the database, I would prefer having a constraint on the table that prevents new rows to be inserted or existing rows to be updated when they violate this time uniqueness.

As stated, my research up to this point was rather disappointing and that is also the reason I cannot really show any code I've tried yet. The most promising approach I followed yet was to create a function that takes a record or the two time period values and the foreign key as input and determines if they overlap with something else. This function could then be called in a check constraint. But after thinking about the amount of cases to check, I gave up because it seemed unreasonable (especially when considering updates as well, which require additional attention).

So my question is if there is some easy way to constraint time slices in SQL Server, without the use of temporal tables (not available in my SQL Server version)? And if yes, how?

Namoshek
  • 6,394
  • 2
  • 19
  • 31
  • Did you consider to create a function based check constraint? – Tyron78 Aug 02 '18 at 09:46
  • It can also be enforced with a trigger but whether you use a trigger or UDF inside a constraint, you can have performance issues. How many records do you have now and how many will you have in five years? To assist with data integrity (and performance) it helps to create a unique constraint on staticid + valid from – Nick.Mc Aug 02 '18 at 10:02
  • @Tyron78 Yes, I did. I also stated this in my question. Probably millions but nowhere near billions. I wouldn't like to create one check function per table though. I have this schema multiple times with different tables. – Namoshek Aug 02 '18 at 15:19
  • What I've done in the past is add a unique constraint on id/startdate as well as a nohter constraint which enforces end>startdate. These don't stop overlapping but they stop really obvious issues and help with performance – Nick.Mc Aug 02 '18 at 22:42
  • @Namoshek Sorry - I missed that point in your OP... anyways, couldn't you prepare a function which takes the tablename as parameter or something? Then you'd have only one function for all your tables. – Tyron78 Aug 03 '18 at 05:56
  • @Tyron78 That's true, I'll look into that. – Namoshek Aug 03 '18 at 06:02
  • @Nick.McDermaid Yeah, I read this suggestion elsewhere already as well. In terms of performance, it definitely helps somewhat, but unfortunately, it won't ensure that my data is consistent. So I need something else. – Namoshek Aug 03 '18 at 06:04
  • 1
    Basically a trigger (real time) or an overnight batch validation job. It's actually a common requirement but you're right - there's no practical solution that involves a database constraint. You don't want to use a trigger or UDF in a constraint because it will eventually impact performance. The next logical solution to implement in the database is to only update the table through a stored proc which will validate it on the way in. But there are limits to this also. – Nick.Mc Aug 03 '18 at 06:26
  • @Nick.McDermaid I see. The more I look into this topic, the more I feel like it will be hard to implement anyway. The suggestion of Tyron78 is not going to work as you cannot use dynamic queries inside UDFs and it is not possible to use SPs in constraints. So maybe my best bet is really to use validation in the application (which is there and works, but suffers from a race condition). – Namoshek Aug 03 '18 at 07:06
  • If the app only adds / changes records through an SP, then you can put the validation logic in an SP and utilise database concurrency to squash race conditions – Nick.Mc Aug 03 '18 at 08:03
  • In a fully generic way, this can't be done elegantly, but that's just because T-SQL is not an elegant language. The checks themselves can be done fairly efficiently with a trigger, in the sense that checking if a new period overlaps any existing one is doable in O(log N), and indexing start and end times of period should be enough to do it in that time. If you're willing to use CLR, you *can* parameterize the tables/columns. This is still suboptimal in the sense that CLR triggers can't figure this out on their own, and they also need to issue separate queries to enforce the constraints. – Jeroen Mostert Aug 03 '18 at 09:22
  • Re: efficiency: you'll have an easier time with both the indexes and the checks if you don't use `null` values for `ValidUntil` rows but "infinity" (`9999-12-31 23:59:59.999999999`, or maybe `9999-09-09 00:00` is slightly easier while still obviously infinitish). This could be translated to `null` in spots where that's more convenient. – Jeroen Mostert Aug 03 '18 at 09:26
  • Using stored procedures brings me back to the problem where I don't want new code for every table I use this feature on. It also makes working with the database more complex (especially in hindsight of the ORM I'm currently using). So I will stick with the current approach and validate everything in my application. Thanks for the suggestion of not using `null` though. It is also something I came across when doing research for this question. Except for minor usability changes, it also does not affect my application at all. – Namoshek Aug 03 '18 at 11:03
  • Although it is a little outdated, check https://stackoverflow.com/q/58199623/1951298 for the fun of it. – Curcuma_ Oct 28 '19 at 20:20
  • 1
    @Curcuma_ Thanks for the link. Yeah, you've got a nice question there and if I understand it correctly, also a slightly more complex one. The answer with the bounty is what I've went with so far. It works, but somehow I thought there might be some better solution out there. Seems not - so far at least. – Namoshek Oct 28 '19 at 20:34

0 Answers0