Prevent overlapping of time events with an indexed view

  • adrian.facio

    SSCrazy

    Points: 2405

    Comments posted to this topic are about the item Prevent overlapping of time events with an indexed view

  • taqveem45

    SSC Journeyman

    Points: 88

    A beautifully elegant solution to the problem.

  • tim.pinder

    Old Hand

    Points: 322

    Don't worry about being late for your weeding anniversary - the garden will be overgrown already! 😀

    Great article though.

  • nbraasch

    SSC Journeyman

    Points: 91

    Is the "WITH SCHEMABINDING" hint necessary to enforce the view index on the table inserts?

  • taqveem45

    SSC Journeyman

    Points: 88

    No, it just means that the underlying table definition cannot be altered without removing the schema-binding dependency first. You can still create views without schema-binding. Any modifications to the base table won't be stopped.

  • nbraasch

    SSC Journeyman

    Points: 91

    from the example, if I'm not mistaken, the statements that are being rolled back are direct inserts to the table, but the index on the view is what is stopping the overlapping minutes from existing. Am I missing something here?

  • taqveem45

    SSC Journeyman

    Points: 88

    nbraasch (11/26/2013)


    from the example, if I'm not mistaken, the statements that are being rolled back are direct inserts to the table, but the index on the view is what is stopping the overlapping minutes from existing. Am I missing something here?

    Correct because they are schema-bound. You can only create indexes on schema-bound views which bind them to the underlying table(s). It tries to satisfy any dependencies it has. You can say that it is almost like the view is being checked for consistency every time it's underlying tables are modified.

  • adrian.facio

    SSCrazy

    Points: 2405

    Thanks buddy =D

  • adrian.facio

    SSCrazy

    Points: 2405

    tim.pinder (11/26/2013)


    Don't worry about being late for your weeding anniversary - the garden will be overgrown already! 😀

    Great article though.

    Thanks =D, heheh i did not thought of that, but anyways i need to be careful right? =D

  • adrian.facio

    SSCrazy

    Points: 2405

    taqveem45 (11/26/2013)


    nbraasch (11/26/2013)


    from the example, if I'm not mistaken, the statements that are being rolled back are direct inserts to the table, but the index on the view is what is stopping the overlapping minutes from existing. Am I missing something here?

    Correct because they are schema-bound. You can only create indexes on schema-bound views which bind them to the underlying table(s). It tries to satisfy any dependencies it has. You can say that it is almost like the view is being checked for consistency every time it's underlying tables are modified.

    Thanks for your comments taqveem45 and nbraasch. Looks like taqveem45 already answered the question, the schema binded indexed views is what is preventing wrong inserts to be saved.

  • adrian.facio

    SSCrazy

    Points: 2405

    adrian.facio (11/26/2013)


    tim.pinder (11/26/2013)


    Don't worry about being late for your weeding anniversary - the garden will be overgrown already! 😀

    Great article though.

    Thanks =D, heheh i did not thought of that, but anyways i will need to be careful right? =D, i'm not married yet but i guessed it was a bad thing to be late =D.

  • taqveem45

    SSC Journeyman

    Points: 88

    adrian.facio (11/26/2013)


    taqveem45 (11/26/2013)


    nbraasch (11/26/2013)


    from the example, if I'm not mistaken, the statements that are being rolled back are direct inserts to the table, but the index on the view is what is stopping the overlapping minutes from existing. Am I missing something here?

    Correct because they are schema-bound. You can only create indexes on schema-bound views which bind them to the underlying table(s). It tries to satisfy any dependencies it has. You can say that it is almost like the view is being checked for consistency every time it's underlying tables are modified.

    Thanks for your comments taqveem45 and nbraasch. Looks like taqveem45 already answered the question, the schema binded indexed views is what is preventing wrong inserts to be saved.

    you're welcome adrian.facio. 🙂

  • Nadrek

    SSC-Insane

    Points: 20039

    This is an interesting take on temporal constraints, but have you tested it with large sets, i.e. perhaps millions of rows of data where the time ranges are measured in years or decades?

  • jjturner

    Right there with Babe

    Points: 728

    This is a decent workaround for something SQL Server doesn't seem to adequately support (afaik): temporal awareness

    Not to plug another rdbms, but the one starting with "p" has gone a long ways towards implementing duration constraints for overlaps and other types of non-/partial-/extra-equi comparisons (or "Exclusion Constraints", as they're referred to).

    Not only that, the PERIOD data type has been generalized to include distance or most any other type of continuous range (cf. "range types").

    Using a Start and End column for a timespan has never made much sense to me. It's one datum, not two disjunct attributes relatable only by their row index.

    It would be nice to see SQL Server deal seriously with this type of data.

    My 2 cantakerous cents :satisfied:

    John

  • lubork

    Newbie

    Points: 1

    Nice simple solution. Observe the Schedule table is fully updateable (e.g. you can change the EndDate value and

    UPDATE Schedule SET EndDate = '2013-12-09 18:30' WHERE Activity like 'Mexico%'

    will work while

    UPDATE Schedule SET EndDate = '2013-12-09 19:40' WHERE Activity like 'Mexico%'

    correctly fails).

    The "supporting" index view may get pretty big (theoretically for one year of activities we may need up to 24*60*365 = 525600 index view entries for one year activities); no problem for SQL Server even if we need to cover several years. Things may get more demanding if we need to drop the time granularity to seconds and watch several independent timelines. I'm sure there exists other solutions; I'm thinking about Itzik's "interval" challenges and algorithms...

Viewing 15 posts - 1 through 15 (of 28 total)

You must be logged in to reply to this topic. Login to reply