• Getting the current record HAS to be a performance hit. What you're talking about is a logging table. I use them all the time and I prefer them for audit tables, but you only go into them when you need point in time data. Why?

    Because of the way indexes work and the mandatory sub-computation to find the most recent record.

    Whenever you use a logging (audit, whatever) table, to find the most recent record you have to go in on the key + Max(DateTimeStamp). So, every time you go in, you have to first aggregate, then pull the current record.

    This gets worse when you want to, say, pull all items with information in a secondary field, say products in category Z. There's two approaches, both... at best... abuse indexing in ways it really doesn't want to go.

    First, you go in, find all recent records, then scan the results.

    Second, you go in and find all records with said category, then go in a second time for most recent record evaluation, then match up the two sets.

    You'll want to keep your histories out of the primary data points if you intend to do anything but work with a single 'record_id' at a time.


    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA