It is trivial to create a Constraint on uniqueness for an integer value, but I am wondering if it is possible to create a constraint on overlap/intersection of a range of values. For example, my schema requirement from a business level is to only allow one Person to reserve one Resource for one particular Time on a particular day. They may wish to claim the resource from 9 AM Jan 2, 2016 until 10:15 AM on Jan 3, 2016, so let's assume the resolution of resource-reservation is 1 minute. The reservation claim could be a tuple (row) in a table where the time (converted to an integer perhaps), plus a resource id (foreign key) must be unique. The third column in this reservation table would be a foreign key to refer to the Person.
The problem with such a schema is that if someone wants to reserve a resource for a week, at one minute resolution, they have to create 1440 rows in the table per day, so that's 10K rows for ONE reservation.
Does SQL Server 2014 (or SQL Server 2016) have any built in way to express that a particular range of values does not overlap any other range entry, in a way that would let me specify the beginning and ending of the reservations as a BeginDateTime/EndDateTime column in a row, and check that the range plus the resource ID form a unique non-overlapping entity?
Until now I have always modelled these constraints in the application layer (above the database) but I think that the power of relational integrity would be useful for preventing people from inserting and updating the reservations in ways that create conflicts.
It would be possible to do something similar by having triggers check for the database values being in an inconsistent state, but I'd rather have the attempt to write an overlapping value blow up, and not commit.
