|
|
Posted by Erland Sommarskog on 05/29/07 21:31
Kurt (nicolas.agrapart@gmail.com) writes:
> I'd like advices about an idea I add to resolve a problem. thanks to
> you in advance for yours answers.
> I have a database with tables that I load with flat file. The size of
> each table is 600 Mb. The flat file are the image of an application
> and there is no updated date or created date on any table. So my
> tables are just a copy of the data from the flat file.
>
> Now I'd like to create an History Table. So I have to determine which
> lines changed and which one did'nt.
> As I don't have any date on my row the only answer I had unil know was
> to check each column on each row to see if any data changed. If the
> data changed I add a new line in my history date.
>
> My idea is to add a checksum column in both table on all columns. To
> know if any data change I just have to check my PK + my checksum
> column.
> Do you think that is a good idea ? Is checksum a quick function or
> not ?.
Neither checksum() nor binary_checksum() are very useful. I think they
based on XOR, and they would too often say a row is unchanged when it
has not. It would be a lot safer to compare all columns?
Exactly how do update your tables? To blow all existing data away and
reload, or do you INSERT new, update existing ones etc? In such case a
timestamp column could work for you. (Timestamp here has nothing to
do with date and time.)
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|