Prevent overlapping of time events with an indexed view

Question

Post reply

Prevent overlapping of time events with an indexed view

adrian.facio

SSCrazy

Points: 2406
More actions
November 25, 2013 at 10:02 pm

#301695

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

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

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

taqveem45 SSC Journeyman Points: 89 More actions · Answer 1

taqveem45

SSC Journeyman

Points: 89

November 26, 2013 at 3:16 am

#1669059

A beautifully elegant solution to the problem.

tim.pinder Old Hand Points: 322 More actions · Answer 2

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

Great article though.

nbraasch SSC Enthusiast Points: 101 More actions · Answer 3

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

taqveem45 SSC Journeyman Points: 89 More actions · Answer 4

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 Enthusiast Points: 101 More actions · Answer 5

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: 89 More actions · Answer 6

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: 2406 More actions · Answer 7

adrian.facio

SSCrazy

Points: 2406

November 26, 2013 at 8:00 am

#1669164

Thanks buddy =D

adrian.facio SSCrazy Points: 2406 More actions · Answer 8

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: 2406 More actions · Answer 9

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: 2406 More actions · Answer 10

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: 89 More actions · Answer 11

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 More actions · Answer 12

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: 788 More actions · Answer 13

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 More actions · Answer 14

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...