Has a row been changed

You can either:

  • Store every column of the row into temporary variables or a temporary table.
  • Store the checksum of the data.

checksum calculates a hash value for the columns in the argument. So if you do a checksum when reading the row, and then again before writing any change, you will know if somebody has changed the data between the time you read it and the time you want to write it.

Applies to:

  • Microsoft SQL Server 2000
  • Microsoft SQL Server 2005

Data used

USE sql911;
go

SELECT EMPNO,EMPNAME,MGRNO
FROM org;
go
EMPNO  EMPNAME          MGRNO
------ ---------------- ------
AA001  HAAS             NULL
AA002  THOMPSON         AA001 
AA003  KWAN             AA001 
AA004  GEYER            AA001 
AA005  STERN            AA003 
AA006  PULASKI          AA003 
AA007  HENDERSON        AA001 
AA008  SPENSER          AA003 
AA009  LUCCHESSI        AA003 
AA010  QUINTANA         AA003 

(10 row(s) affected)

Checksum

SELECT EMPNO,EMPNAME,MGRNO,
       CHECKSUM(EMPNO,EMPNAME,MGRNO) AS 'Checksum specific',
       CHECKSUM(*) AS 'Checksum general'
FROM org;
go
EMPNO  EMPNAME          MGRNO  Checksum specific Checksum general
------ ---------------- ------ ----------------- ----------------
AA001  HAAS             NULL   -116498689        -116498689
AA002  THOMPSON         AA001  1138573277        1138573277
AA003  KWAN             AA001  -2038928267       -2038928267
AA004  GEYER            AA001  -1892524235       -1892524235
AA005  STERN            AA003  -1941826697       -1941826697
AA006  PULASKI          AA003  -33671250         -33671250
AA007  HENDERSON        AA001  -1369490498       -1369490498
AA008  SPENSER          AA003  307968829         307968829
AA009  LUCCHESSI        AA003  522322332         522322332
AA010  QUINTANA         AA003  972278707         972278707

(10 row(s) affected)
  • There is very low probability that you will get a duplicate value if there is different data.
  • The checksum returns an integer value.
  • You should NOT use: checksum(*). The value will be different if somebody modifies the table structure such as adding or deleting a column.
  • You should specify the columns in the checksum arguments.
  • If you store the checksum in a table and create an index on it, MSSQL will create an hash index.