I am working on a rather large SQL Database project that requires some history tracking. I am aware that SQL has things like Data Capture but I need to have more control than just storing backup copies of the data along with other requirements.
Here is what I am trying to do, I found some similar questions on here like
get-the-smallest-datetime-value-for-each-day-in-sql-database
and
how-can-i-truncate-a-datetime-in-sql-server
What I am trying to avoid is exactly the types of things that are mentioned in these answers, by that I mean that they require some sort of conversion during the Query i.e. truncating the DateTime value, what I would like to do is take the DateTime.Ticks or some other DateTime property, and do a one way conversion to a smaller type to create a "Version" of each record that can be quickly Queried without doing any kind of Conversion after the database update/insert.
The concern that I have about simply storing the long from the DateTime.Ticks as a Version or using something like a Base36 string is the size and the time that is required during queries to compare the fields.
Can anyone point me in the right direction on how to SAFELY convert a DateTime.Ticks or a long into something that can be directly compared during queries? By this I mean I would like to be able to locate the history record using something like:
int versionToFind = GetVersion(DateTime.Now);
var result = from rec in db.Records
where rec.version <= versionToFind
select rec;
or
int versionToFind = record.version;
var result = from rec in db.Records
where rec.version >= versionToFind
select rec;
One thing to mention here is that I am not opposed to using some other method of quickly tracking the History of the data. I just need to end up with the quickest and smallest solution to be able to generate and compare Versions for each record.
