bjames60689 (1/28/2015)
I understand what you are saying. Do you feel the same way when it is more exact, such as millisecond (2000-06-11 23:59:59.990)?Basically I want to make it so that they cannot screw up start and and end dates by leaving gaps between the previous end date and the new start date as they maintain the calendar.
If the BucketEndDate is not the BucketStartDate of the temporally next row, then it's a mistake in my opinion even if you get down to the 100-nanosecond range available in the "newer" datatypes. There's no need for it and having even the tiniest gap can (will?) complicate your life and the code immensely.
To automate such changes, I'd use a well written trigger to automate the cascading update. Looking at your indexes, I don't see any on the BucketStartDate or BucketEndDate and you cannot rely on the BucketID because there could be gaps. You'll need to test what indexes would benefit the trigger. I suppose you could use something like Lead/Lag/etc here but haven't played with it well enough to known.
--Jeff Moden
Change is inevitable... Change for the better is not.