Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » SQL Server 2005 » SQL Server 2005 Integration Services » Checksum function???
Add to briefcase
17 posts, Page 2 of 2
««12

Checksum function??? Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
JustANumber
Posted Tuesday, September 01, 2009 10:24 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, June 16, 2010 10:55 AM
Points: 33, Visits: 113
I did this recently to determine if a row needed to be updated. I put a checksum column in the table using the values that could possibly be updated. The stored procedure had a checksum variable based on the input variables. The input variables for the proc are also the DB columns that could possibly be updated.

By comparing the DB checksum calculation with the stored procedure checksum variable I knew whether the row needed updating or not.
Post #780876
GSquared
Posted Tuesday, September 01, 2009 11:20 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 1:55 PM
Points: 15,442, Visits: 9,571
Mark Pratt (9/1/2009)
I did this recently to determine if a row needed to be updated. I put a checksum column in the table using the values that could possibly be updated. The stored procedure had a checksum variable based on the input variables. The input variables for the proc are also the DB columns that could possibly be updated.

By comparing the DB checksum calculation with the stored procedure checksum variable I knew whether the row needed updating or not.


Makes sense.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #780908
ducon
Posted Wednesday, September 02, 2009 11:38 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, June 05, 2012 9:24 AM
Points: 118, Visits: 253
Mark Pratt (9/1/2009)
I did this recently to determine if a row needed to be updated. I put a checksum column in the table using the values that could possibly be updated. The stored procedure had a checksum variable based on the input variables. The input variables for the proc are also the DB columns that could possibly be updated.

By comparing the DB checksum calculation with the stored procedure checksum variable I knew whether the row needed updating or not.


Hi,

Yep, this is exactly the point I was trying to make. Note that is very easily doable using a script component in a data flow and from what we've tried to far, goes pretty fast.

Regards,

Greg
Post #781666
sqlrunner
Posted Friday, September 04, 2009 8:37 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, November 29, 2012 8:08 AM
Points: 29, Visits: 155
If you are using sql 2005, and the columns you are interested in are varchar,nvarchar, or varbinary, you can try making a computed column out of the /b HashBytes /b function. There are a few limits, one being the total input size cannot be greater than 8k ( I think this was fixed in either sp3 or 2008). I also believe it is deterministic so you can put an index on it. Use the SHA1 algorithm as the chance of collision is 1 in 2^80.

Josef Richberg
2009 Exceptional DBA
http://www.josef-richberg.squarespace.com
http://twitter.com/sqlrunner
Post #782973
John Rowan
Posted Tuesday, September 08, 2009 9:53 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, January 29, 2013 10:54 AM
Points: 3,837, Visits: 3,821
Is there any sort of date column in the source table that could show you which rows have been updated? If you could do something to avoid comparing the million rows, that may help.

Also, have you tried the SCD transformation? Between that and proper indexing on your dimension, you should still get decent performance out of the compare.

How much free reign do you have on the source system? Would it be possible to make changes there to aid in making this easier?



John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
Post #784362
vishal.gamji
Posted Thursday, September 10, 2009 7:29 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 1:20 PM
Points: 536, Visits: 476
I have found the checksum to be useful in some cases and unreliable in some. If the data set if distinct then the checksum function (of all rows stands good), but in another case i had to get 27 Million rows from DB2 with no primary key, no uniqueness on the table, so i thought of using checksum to compare the rows in a staging environment. Not only did i have the overhead of creating the checksum, i also had to compare it + the checksum gives me a integer value based on the row uniqueness, where i had duplicates and everything was getting messed up..... so in short i would think the usage totally depends on the case.
Post #785674
John Rowan
Posted Thursday, September 10, 2009 10:26 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, January 29, 2013 10:54 AM
Points: 3,837, Visits: 3,821
The OP has not responded for quite some time now.

SQL Learner? Are you still working on this? We'd appreciate some feedback as to how you've resolved the problem. Having that in the thread will help others that come accross this thread down the road.



John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
Post #785829
« Prev Topic | Next Topic »

Add to briefcase
17 posts, Page 2 of 2
««12

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.