Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
nlocklin
Yak Posting Veteran
69 Posts |
 Posted - 2006-10-06 : 15:21:25
|
| I have two tables:Table One---------RecordIDColumn AColumn BColumn CTable Two---------ChangeIDRecordIDColumnChangedOldValueNewValueDtChangedTable Two is basically a change history table. Any time a value in a column in table One is changed, a record is inserted into table Two indicating the RecordID of the record that was changed, which column, what the old and new values are, and when it was changed.I have been given the assignment of creating a view that will show the original values for the records in table One as they were inserted. I've managed to come up with something that works but it's very slow and I'm almost certain there's a better way. Can anyone help me find the most efficient solution?Thanks in advance!--"It's not that I'm lazy... it's that I just don't care." |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-10-06 : 17:40:07
|
| It might help if you posted the code you are having trouble with.I doubt that you will get very good performacne, since the second table is an entity attribute value design. That almost always results in extremely poor query performance.CODO ERGO SUM |
 |
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2006-10-07 : 01:13:19
|
| Is a newly inserted record also inserted into table two? Or is it only inserted into table two after the original records is modified.In other words, does table two hold a complete set of records including all the records from table one?"Once you establish possibility, all that remains are desire and determination." |
|
|
|
nlocklin
Yak Posting Veteran
69 Posts |
Posted - 2006-10-09 : 10:52:39
|
quote:
Originally posted by blindmanIs a newly inserted record also inserted into table two? Or is it only inserted into table two after the original records is modified.In other words, does table two hold a complete set of records including all the records from table one?
Table two only contains the changes, not the initial insert.--"It's not that I'm lazy... it's that I just don't care." |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2006-10-09 : 11:03:51
|
| I guess most of us do this...but to make sure the trigger that fires does not impact any other OLTP performance, I whoulds never have an index on the history table.....so the table will scanSo....if you want to do reporting, make another table and index thatAlso, you are taking the more complicated route...I just move the entire row to history, and not worrying about what column changed...you can figure that out laterBrett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxAdd yourself!http://www.frappr.com/sqlteam |
|
|
|
nforakis
Starting Member
1 Post |
Posted - 2006-10-17 : 09:06:40
|
I'm curious how you figured out which cloumn changed and how to just insert the changes into the history table?quote:
Originally posted by nlocklin
I have two tables:Table One---------RecordIDColumn AColumn BColumn CTable Two---------ChangeIDRecordIDColumnChangedOldValueNewValueDtChangedTable Two is basically a change history table. Any time a value in a column in table One is changed, a record is inserted into table Two indicating the RecordID of the record that was changed, which column, what the old and new values are, and when it was changed.I have been given the assignment of creating a view that will show the original values for the records in table One as they were inserted. I've managed to come up with something that works but it's very slow and I'm almost certain there's a better way. Can anyone help me find the most efficient solution?Thanks in advance!--"It's not that I'm lazy... it's that I just don't care."
|
|
|
|
Kristen
Test
22859 Posts |
|
|
|
|
|
|
|
Querying a table with a "history" table