Unique Constraint on DateTime range values

Question

Unique Constraint on DateTime range values

mike gercevich

SSCrazy

Points: 2961
More actions
February 3, 2008 at 10:44 am

#154106

Is there any way to implement a unique constraint based off a calculated timestamp range?
Requirements: Each [UnitID] has a [ReadingValue] and a [ReadingTimestamp]. The Unit broadcasts its ID and ReadingValue at specified intervals (15 minutes). Since the "Unit" does not have an internal clock, ReadingListeners receive the broadcasts, store the values with an internal system timestamp, and later attempt to push the reading value in the DB.
There is a potential for multiple Listeners to receive the broadcast and later attempt to insert the ReadingValue into the DB table. The Listeners do not have synchronized internal clocks. [UnitID] and [ReadingValue] need to be unique based on a [ReadingTimestamp] range of the 15 minute intervals.
I need to create a high performance insert, that will not allow the insert to succeed if another [UnitID] and [ReadingTimestamp] +/- 7.5 minutes exists. Can a Unique Index Constraint be created based off of a calculated range based off the current timestamp parameter?
The I understand that an IF EXISTS block could be used to check for records within the calculated range, and only insert based on non-existence. This does not work in an extremely high-volume situation (>10K insertions/sec.), and will create duplicated records. I cannot hold locks or set transaction isolation levels to SERIALIZABLE to enforce the range duplication checks, or the performance will degrade from the lock waits.
Anyone have a solution to enforce this type of range unique constraint check?
Thanks,
-Mike

Viewing 4 posts - 1 through 3 (of 3 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

You should be able to create a calculated in the table, then put a unique index on that. It requires quite a few of the connection settings to have specific values, but it should work.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Ian Yates SSCoach Points: 19738 More actions · Answer 2

As Gail said, create a calculated column on your table which contains the time rounded to the nearest 7.5 minute value - perhaps call it timeRounded. Then create a unique index on this column along with your unitID - the index would be (unitID, timeRounded).

You need to look in SQL Server Books Online about indexed views and the SET options that need to be specified. In my opinion those SET options should be always set that way anyway so I usually alter the server's default SET options to suit.

mike gercevich SSCrazy Points: 2961 More actions · Answer 3

Yes, I believe a calculated column is probably the way to go, and by putting the unique constraint on the pair of columns, performance would still be excellent.

Thanks for your input,

-Mike