CHECKSUM SQL function

1. Usage of CHECKSUM using integer value

SQL Server Query 1

            
SELECT  

    CHECKSUM(100) AS TestCheckSum;  -- Returns an integer checksum value  

            
        

Create SQL query with SqlQueryBuilder 1

            
var (sql1, parameters1) = new SqlQueryBuilder()  

    .Select()  

    .Column(new CHECKSUM(100), "TestCheckSum")  

    .Build();  

            
        

Query build by SqlQueryBuilder 1

            
SELECT CHECKSUM(@pMAIN_2507200147312989380) AS TestCheckSum;


            
        

Parameters (If used)

Query Results 1:

2. Usage of CHECKSUM using string value

SQL Server Query 2

            
SELECT  

    CHECKSUM('Hello') AS TestCheckSum; -- Returns an integer checksum value  

            
        

Create SQL query with SqlQueryBuilder 2

            
var (sql2, parameters2) = new SqlQueryBuilder()  

    .Select()  

    .Column(new CHECKSUM("Hello"), "TestCheckSum")  

    .Build();  

            
        

Query build by SqlQueryBuilder 2

            
SELECT CHECKSUM(@pMAIN_2507200147313010520) AS TestCheckSum;


            
        

Parameters (If used)

Query Results 2:

3. Usage of CHECKSUM using multiple values

SQL Server Query 3

            
SELECT  

    CHECKSUM(10, 'Test', 10.5) AS TestCheckSum;  

            
        

Create SQL query with SqlQueryBuilder 3

            
var (sql3, parameters3) = new SqlQueryBuilder()  

    .Select()  

    .Column(new CHECKSUM(10, "Test", 10.5), "TestCheckSum")  

    .Build();  

            
        

Query build by SqlQueryBuilder 3

            
SELECT CHECKSUM(@pMAIN_2507200147313021280, @pMAIN_2507200147313021281, @pMAIN_2507200147313021282) AS TestCheckSum;


            
        

Parameters (If used)

Query Results 3:

4. Usage of CHECKSUM using different data types

SQL Server Query 4

            
SELECT  

    CHECKSUM(1, 'abc', GETDATE(), 123.45) AS TestCheckSum;  

            
        

Create SQL query with SqlQueryBuilder 4

            
var (sql4, parameters4) = new SqlQueryBuilder()  

    .Select()  

    .Column(new CHECKSUM(new CHECKSUM(1, "abc", new GETDATE(), 10.5), "TestCheckSum")  

    .Build();  

            
        

Query build by SqlQueryBuilder 4

            
SELECT CHECKSUM(@pMAIN_2507200147313032140, @pMAIN_2507200147313032141, GETDATE(), @pMAIN_2507200147313032142) AS TestCheckSum;


            
        

Parameters (If used)

Query Results 4:

5. Usage of CHECKSUM to demonstrate that orders matter

SQL Server Query 5

            
SELECT CHECKSUM('abc', 'def') AS TestCheckSum  

UNION  

SELECT CHECKSUM('def', 'abc') AS TestCheckSum;  -- Different order, different checksum  

            
        

Create SQL query with SqlQueryBuilder 5

            
var (sql5, parameters5) = new SqlQueryBuilder()  

    .Select()  

    .Column(new CHECKSUM("abc", "def"), "TestCheckSum")  

    .Union(new SqlQueryBuilder()  

        .Select()  

        .Column(new CHECKSUM("def", "abc"), "TestCheckSum")  

    )  

    .Build();  

            
        

Query build by SqlQueryBuilder 5

            
SELECT CHECKSUM(@pMAIN_2507200147313041510, @pMAIN_2507200147313041511) AS TestCheckSum
UNION
SELECT CHECKSUM(@pMAIN_2507200147313041512, @pMAIN_2507200147313041513) AS TestCheckSum;


            
        

Parameters (If used)

Query Results 5:

6. Usage of CHECKSUM for a column in products table

SQL Server Query 6

            
SELECT CHECKSUM(ProductName) AS ProductNameCheckSum FROM Products;  

            
        

Create SQL query with SqlQueryBuilder 6

            
var (sql6, parameters6) = new SqlQueryBuilder()  

    .Select()  

    .Column(new CHECKSUM(new Column("ProductName")), "ProductNameCheckSum")  

    .From("Products")  

    .Build();  

            
        

Query build by SqlQueryBuilder 6

            
SELECT CHECKSUM(ProductName) AS ProductNameCheckSum
FROM Products;


            
        

Parameters (If used)

Query Results 6:

7. Usage of CHECKSUM for a column in customers table

SQL Server Query 7

            
SELECT  

    CustomerID,  

    ContactName,  

    CHECKSUM(CustomerID, ContactName) AS CustomerInfoChecksum  

FROM Customers;  

            
        

Create SQL query with SqlQueryBuilder 7

            
var (sql7, parameters7) = new SqlQueryBuilder()  

    .Select()  

    .Columns("CustomerID","ContactName")  

    .Column(new CHECKSUM(new Column("CustomerID"), new Column("ContactName")), "CustomerInfoChecksum")  

    .From("Customers")  

    .Build();  

            
        

Query build by SqlQueryBuilder 7

            
SELECT CustomerID,
       ContactName,
       CHECKSUM(CustomerID, ContactName) AS CustomerInfoChecksum
FROM Customers;


            
        

Parameters (If used)

Query Results 7:

8. Usage of CHECKSUM to find changes in the values

SQL Server Query 8

            
-- Keep original contactname, customerid current and changed checksum in temp table  

SELECT CustomerID, ContactName, 0 AS OriginalChecksum, 0 AS CurrentChecksum   

INTO #ForCustomerChecksum  

FROM Customers  

WHERE CustomerID = 'ALFKI';  

-- Update temp table with current checksum  

UPDATE #ForCustomerChecksum  

SET OriginalChecksum = (SELECT CHECKSUM(CustomerID, ContactName, City)  

						FROM Customers  

						WHERE CustomerID = 'ALFKI')  

WHERE CustomerID = 'ALFKI';  

-- update customer contact number for a customerid  

UPDATE Customers  

SET ContactName = 'New Contact Name'  

WHERE CustomerID = 'ALFKI';  

-- update temp table with new/changed checksum  

UPDATE #ForCustomerChecksum  

SET CurrentChecksum = (SELECT CHECKSUM(CustomerID, ContactName, City)  

					   FROM Customers  

						WHERE CustomerID = 'ALFKI')  

WHERE CustomerID = 'ALFKI';  

-- compare old and new checksum  

SELECT  

    OriginalChecksum,  

    CurrentChecksum,  

    CASE  

        WHEN OriginalChecksum = CurrentChecksum THEN 'No Change'  

        ELSE 'Change Detected'  

    END AS ChangeStatus  

FROM #ForCustomerChecksum  

-- update back the original contact name for selected customer id  

UPDATE Customers  

SET ContactName = (SELECT ContactName  

				   FROM #ForCustomerChecksum  

				   WHERE CustomerID = 'ALFKI')  

WHERE CustomerID = 'ALFKI';  

-- drop temp table  

DROP TABLE #ForCustomerChecksum  

            
        

Create SQL query with SqlQueryBuilder 8

            
var (sql8, parameters8) = new SqlQueryBuilder()  

    .Select()  

    .Columns("CustomerID", "ContactName")  

    .Column(0, "OriginalChecksum")  

    .Column(0, "CurrentChecksum")  

    .INTO(new Table("#ForCustomerChecksum"))  

    .From("Customers")  

    .Where(new Where(new Column("CustomerID").Equale("ALFKI")))  

    .Update("#ForCustomerChecksum", new List<ISetValue>()   

    {  

        new SetValue("OriginalChecksum", new SqlQueryBuilder()  

            .Select()  

            .Column(new CHECKSUM(new Column("CustomerID"), new Column("ContactName"), new Column("City")), "CheckSum")  

            .From("Customers")  

            .Where(new Where(new Column("CustomerID").Equale("ALFKI"))))  

    })  

    .Update("Customers", new List<ISetValue>()   

    {  

        new SetValue("ContactName", "New Contact Name")  

    })  

    .Where(new Where(new Column("CustomerID").Equale("ALFKI")))  

    .Update("#ForCustomerChecksum", new List<ISetValue>()   

    {  

        new SetValue("CurrentChecksum", new SqlQueryBuilder()  

            .Select()  

            .Column(new CHECKSUM(new Column("CustomerID"), new Column("ContactName"), new Column("City")), "CheckSum")  

            .From("Customers")  

            .Where(new Where(new Column("CustomerID").Equale("ALFKI"))))  

    })  

    .Where(new Where(new Column("CustomerID").Equale("ALFKI")))  

    .Select()  

    .Columns("OriginalChecksum","CurrentChecksum")  

    .Column(new CASE()  

        .When(new Column("OriginalChecksum").Equale(new Column("CurrentChecksum"))).Then("No Change")  

        .Else("Change Detected")  

    , "ChangeStatus")  

    .From("#ForCustomerChecksum")  

    .Update("Customers", new List<ISetValue>()   

    {  

        new SetValue("ContactName", new SqlQueryBuilder()  

            .Select()  

            .Column("ContactName", "ContactName")  

            .From("#ForCustomerChecksum")  

            .Where(new Where(new Column("CustomerID").Equale("ALFKI"))))  

    })  

    .Where(new Where(new Column("CustomerID").Equale("ALFKI")))  

    .DropTable(new Table("#ForCustomerChecksum"))  

    .Build();  

            
        

Query build by SqlQueryBuilder 8

            
SELECT CustomerID,
       ContactName,
       @pMAIN_2507200147313188260 AS OriginalChecksum,
       @pMAIN_2507200147313188261 AS CurrentChecksum
INTO #ForCustomerChecksum
FROM Customers
WHERE CustomerID = @pMAIN_2507200147313188262;

UPDATE #ForCustomerChecksum
SET OriginalChecksum = (SELECT CHECKSUM(CustomerID, ContactName, City) AS CheckSum
                        FROM Customers
                        WHERE CustomerID = @pMAIN_2507200147313188263);

UPDATE Customers
SET ContactName = @pMAIN_2507200147313188264
WHERE CustomerID = @pMAIN_2507200147313188265;

UPDATE #ForCustomerChecksum
SET CurrentChecksum = (SELECT CHECKSUM(CustomerID, ContactName, City) AS CheckSum
                       FROM Customers
                       WHERE CustomerID = @pMAIN_2507200147313188266)
WHERE CustomerID = @pMAIN_2507200147313188267;

SELECT OriginalChecksum,
       CurrentChecksum,
       CASE WHEN OriginalChecksum = CurrentChecksum THEN @pMAIN_2507200147313188268 ELSE @pMAIN_2507200147313188269 END AS ChangeStatus
FROM #ForCustomerChecksum;

UPDATE Customers
SET ContactName = (SELECT ContactName AS ContactName
                   FROM #ForCustomerChecksum
                   WHERE CustomerID = @pMAIN_250720014731318826_10)
WHERE CustomerID = @pMAIN_250720014731318826_11;

DROP TABLE #ForCustomerChecksum;


            
        

Parameters (If used)

Query Results 8: