Solved

checksum autogeneration

Posted on 2013-06-05
6
497 Views
Last Modified: 2013-06-11
hi guys
I want to create a table Customer with 4 columns

Customer
Id Name Price Checksum


checksum value needs to be autopopulated when an insert occurs
and on update of that row the checksum should update aswell
does sql server provide any such feature?

I am using md5 checksum in my java front end , so that way i can check if the row already exists in the table.

thanks
0
Comment
Question by:royjayd
  • 3
  • 2
6 Comments
 

Author Comment

by:royjayd
ID: 39223207
experts,
Can anyone please help with this. I want to know if checksum can be calculated and saved for each row

thanks
0
 
LVL 65

Accepted Solution

by:
Jim Horn earned 200 total points
ID: 39223490
Define for us in standard English terms what you mean by 'checksum', keeping in mind that what I know about md5 and Java can be written on the back of a matchbook cover, in large letters, with a grease pencil.
0
 

Author Comment

by:royjayd
ID: 39223533
Thanks for responding..

By 'checksum' i mean a unique code for a row of data
So each row will have its own checksum or unique code.
I looked around and see that sql server has a checksum function so in my case for
above Customer table  it would be
CHECKSUM(Id,Name,Price);

how ever what i want to do is
when a new row is inserted into the Customer table create the CHECKSUM for it and insert it in the Checksum column
i tried something like

CREATE TABLE [ado].[CUSTOMER](
      [ID] [nvarchar](16) NULL,
      [Name] [nvarchar](100) NULL,
      [Price] [nvarchar](100) NULL,      
      [CheckSum] [nvarchar](100) DEFAULT CHECKSUM([ado].[CUSTOMER].ID,[CUSTOMER].Name,[CUSTOMER].Price)

but it doesnt seem to work.

Thanks
0
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
LVL 48

Assisted Solution

by:PortletPaul
PortletPaul earned 200 total points
ID: 39224366
you are attempting to avoid testing if a record does actually exist in the data persistence layer by using a calculation in the business logic layer

too many things could go wrong with this and I would not recommend it.

but hashbytes() will provide an MD5:
http://msdn.microsoft.com/en-us/library/ms174415.aspx

this can be coupled with "computed column" PERSISTED
http://msdn.microsoft.com/en-us/library/ms191250(v=sql.105).aspx

also ...
>>  [Price] [nvarchar](100) NULL,      
numeric data in nvarchar is generally a bad idea - it causes a great deal of overhead in all queries when adding/summing/filtering etc.
0
 

Author Comment

by:royjayd
ID: 39226871
the point of this excercise is i have two tables

TableA and TableB
with exact same number of columns but different data

TableA
col1 col2 col3  checksum

TableB
col1 col2 col3  checksum


Now TableA gets refreshed every 30 minutes with new data from a remote database table

The new data which arrived in TableA needs to be inserted in TableB.

So what i have in mind is if each row has its own checksum in both the tables,
compare the checksum for each row in TableA v/s TableB

The checksum(s) in TableA missing in TableB are the new rows, so add them to TableB


Do you think my approach is wrong ?

thanks
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39227813
What I had in my mind when expressing the opinion before didn't involve table synchronization.

Other experts here will be far more experienced than I am on this table synchronization need
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Troubleshooting Methodology - steps 3 36
Sql query to Stored Procedure 6 39
Authentication error 1 39
SQL Query 3 49
     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now