Sql-server – how I can create check sum to validate no changes at int string pairs on table

data integritydatabase-designnumber formattingsql server

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.

Best Answer

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
Related Question