Check Digit Vertical and Horizontal

• Download source - 868 B

Introduction

This tip explains how to calculate the Check Digit Vertical (CDV) and Horizontal (CDH) by SQL Server.

Using the Code

For this example, we will use a temporary table so it can be implemented and tested in any SQL.

CREATE TABLE #TableTemporal (Field1 int, Field2 varchar(50), CDH int) 

Each insert must calculate the CDH (CHECKSUM).

INSERT INTO #TableTemporal VALUES (1,'First field', BINARY_CHECKSUM(1,'First field'))
INSERT INTO #TableTemporal VALUES (2,'Second field', BINARY_CHECKSUM(2,'Second field'))
INSERT INTO #TableTemporal VALUES (3,'Third field', BINARY_CHECKSUM(3,'Third field'))
INSERT INTO #TableTemporal VALUES (4,'Fourth field', BINARY_CHECKSUM(4,'Fourth field')) 

We control everything is inserted and the CDH calculated.

select * from #TableTemporal

Now calculate the Check Digit Vertical:

SELECT  CHECKSUM_AGG(CDH) as CDV
FROM    #TableTemporal 

In the next example, we will change records and see how they change the CDH and CDV.

We modify a record:

update #TableTemporal
set Field2 = 'First field modified'
where Field1 = 1 

To modify a record should be re recalculate the CDH (CHECKSUM).

update #TableTemporal
set CDH = BINARY_CHECKSUM(Field1, Field2)
where Field1 = 1 

We compared the CDH of the first record with the previous and see that it has changed.

select * from #TableTemporal

Calculation on the column CDH CDV after modification:

SELECT  CHECKSUM_AGG(CDH) as CDV
FROM    #TableTemporal 

Now we will verify that the digits are correct Horizontal Verifiers and no record has been modified outside of our system.

If everything is correct, it should not return any record.

SELECT *
FROM #TableTemporal
WHERE CDH <> BINARY_CHECKSUM(Field1, Field2) 

Then we will simulate the modified record outside our system.

We again modify the first record but without updating your CDH.

update #TableTemporal
set Field2 = 'First field modified 2'
where Field1 = 1 

If everything is correct, you must return the first record.

SELECT *
FROM #TableTemporal
WHERE CDH <> BINARY_CHECKSUM(Field1, Field2)