Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Business case is.
1)Users print document on paper
2)users carry printed paper to Boss, Boss sing on paper
3)after that users going to administrator to digitally sign that paper.

My task is to help administrator to on easy way be sure that there is no changes between paper document and digital document.

Document is written in three sql tables.
On 1st table integrity is string+date
On 2nd table integrity is combination int+float
On 3rd table integrity is int+string.

There is others columns in these tables which can be updated freely after Boss sing document without needs to Boss sing it again.

My idea is to from these three table / combination make some kind of SHA or CheckSum. Which is going to be same while there is no changes on document.
I do not want to my checksum be infinite or too long so administrator can easily check is there violation on my custom integrity.

I start thinking that I can for int - float make sum( int * folat)
For stings for each letter I can use ASCII number and do sum(int * (ascii(S)+ascii(T)+ascii(I)+ascii(N)+ascii(G))).
For date - int same make int * (date to int) After that I can totalize (sum) these three numbers and convert to HEX. But perhaps my number is going to be out of range or too large for comparing by human eye.

How I can shrink extra large numbers to be easily readable and comparable by humans ? Is there another way to I ensure that there was no changes on my protected columns in one period of time ?

I am using MSSQL2005/8 server. My reporting engine is most of rime SqlReportingServices, but I prefer to do this loginc on database level not reporting level.

I do not want to lock editing of document because sometimes Boss push users to go back and correct document before signing.
I also can't push boss to take mouse in hand.

share|improve this question

1 Answer 1

up vote 3 down vote accepted

You could just use the CHECKSUM function as it returns an INT. Just put in the columns that you want to checksum against for those tables and it'll output the checksum value for those columns. Here's an example from AdventureWorks2012 but this function has been around for quite a while. In this case I've got columns from two tables as part of the single checksum function call.

select loginID, checksum(LoginID, JobTitle, ShiftID)
from HumanResources.employee
join HumanResources.EmployeeDepartmentHistory on employee.BusinessEntityID = EmployeeDepartmentHistory.BusinessEntityID
and EndDate is null
share|improve this answer
    
Shame on me, I read several books about SQL and I can't remember that I saw anything about check-sum function. –  adopilot Jun 15 '13 at 21:22
    
Also look at binary_checksum as well. The output is still an INT, but it does it's checking at the binary level so A <> a with binary_checksum but with checksum A=a. –  mrdenny Jun 15 '13 at 21:24

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.