CHECKSUM_AGG SQL function

1. Usage of CHECKSUM_AGG to find changes in Order Details

SQL Server Query 1

            
SELECT  

    o.OrderID,  

    CHECKSUM_AGG(CAST(o.Freight AS INT)) OVER (PARTITION BY o.EmployeeID) AS OrderChecksum,  -- Check for changes in Orders  

    CHECKSUM_AGG(od.Quantity * CAST(od.UnitPrice AS INT) * (1 - CAST(od.Discount AS INT))) AS OrderDetailsChecksum -- Check for changes in Order Details  

INTO #OrderChecksums  

FROM Orders AS o  

JOIN [Order Details] AS od ON o.OrderID = od.OrderID  

GROUP BY o.OrderID, o.Freight, o.EmployeeID  

SELECT * FROM #OrderChecksums  

DROP TABLE #OrderChecksums;  

            
        

Create SQL query with SqlQueryBuilder 1

            
var (sql1, parameters1) = new SqlQueryBuilder()  

    .Select()  

    .Column("o.OrderID", "OrderID")  

    .Column(new CHECKSUM_AGG(new CAST(new Column("o.Freight"), SqlDataType.INT))  

        .OVER(new OVER().PARTITION_BY(new Column("o.EmployeeID"))), "OrderChecksum")  

    .Column(new CHECKSUM_AGG(new ColumnArithmatic("od.Quantity").MULTIPLY(new CAST(new Column("od.UnitPrice"), SqlDataType.INT))  

        .MULTIPLY().StartBracket(1).SUBTRACT(new CAST(new Column("od.Discount"), SqlDataType.INT)).EndBracket()  

    ), "OrderDetailsChecksum")  

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

    .From("Orders", "o")  

    .Join(new List<IJoin>()   

    {  

        new INNERJOIN().TableName("[Order Details]","od")  

            .On(new Column("o.OrderID").Equale(new Column("od.OrderID")))  

    })  

    .GroupBy(new GroupBy("o.OrderID","o.Freight","o.EmployeeID"))  

    .Select().Star().From("#OrderChecksums")  

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

    .Build();  

            
        

Query build by SqlQueryBuilder 1

            
SELECT o.OrderID AS OrderID,
       CHECKSUM_AGG(CAST (o.Freight AS INT)) OVER (PARTITION BY o.EmployeeID) AS OrderChecksum,
       CHECKSUM_AGG(od.Quantity * CAST (od.UnitPrice AS INT) * (@pMAIN_2507200155149736170 - CAST (od.Discount AS INT))) AS OrderDetailsChecksum
INTO #OrderChecksums
FROM Orders AS o
     INNER JOIN
     [Order Details] AS od
     ON o.OrderID = od.OrderID
GROUP BY o.OrderID, o.Freight, o.EmployeeID;

SELECT *
FROM #OrderChecksums;

DROP TABLE #OrderChecksums;


            
        

Parameters (If used)

Query Results 1:

2. Usage of CHECKSUM for full contact of customer

SQL Server Query 2

            
SELECT CHECKSUM_AGG(BINARY_CHECKSUM(CustomerID, (ContactName + ' (' + ContactTitle + ')'))) AS FullContactChecksum  

FROM Customers;  

            
        

Create SQL query with SqlQueryBuilder 2

            
var (sql2, parameters2) = new SqlQueryBuilder()  

    .Select()  

    .Column(new CHECKSUM_AGG(  

        new BINARY_CHECKSUM(new Column("CustomerID"), new CONCAT(new Column("ContactName"), " ("),  

            new Column("ContactTitle"), ")")), "FullContactChecksum")  

    .From("Customers")  

    .Build();  

            
        

Query build by SqlQueryBuilder 2

            
SELECT CHECKSUM_AGG(BINARY_CHECKSUM(CustomerID, CONCAT(ContactName, @pMAIN_2507200155149915690), ContactTitle, @pMAIN_2507200155149915691)) AS FullContactChecksum
FROM Customers;


            
        

Parameters (If used)

Query Results 2:

3. Usage of CHECKSUM for customer postal code

SQL Server Query 3

            
SELECT CHECKSUM_AGG(BINARY_CHECKSUM(CustomerID, CAST(PostalCode AS VARCHAR(10)))) AS PostalCodeVarcharChecksum  

FROM Customers;  

            
        

Create SQL query with SqlQueryBuilder 3

            
var (sql3, parameters3) = new SqlQueryBuilder()  

    .Select()  

    .Column(new CHECKSUM_AGG(new BINARY_CHECKSUM(new Column("CustomerID"), new CAST(new Column("PostalCode"), SqlDataType.VARCHAR, "10")))  

    , "PostalCodeVarcharChecksum")  

    .From("Customers") 

    .Build();  

            
        

Query build by SqlQueryBuilder 3

            
SELECT CHECKSUM_AGG(BINARY_CHECKSUM(CustomerID, CAST (PostalCode AS VARCHAR (10)))) AS PostalCodeVarcharChecksum
FROM Customers;


            
        

Parameters (If used)

Query Results 3:

4. Usage of CHECKSUM to show change in customer data

SQL Server Query 4

            
WITH PreviousCustomers AS (  

     SELECT CustomerID, CompanyName, ContactName -- ... other columns  

     FROM Customers -- A hypothetical table storing previous customer data  

     WHERE CustomerID IN ('ALFKI','ANATR','ANTON','AROUT','BERGS')  

 )  

 SELECT  

     c.CustomerID,  

     CASE  

         WHEN BINARY_CHECKSUM(c.CompanyName, c.ContactName) <>  

              BINARY_CHECKSUM(pc.CompanyName, pc.ContactName) THEN 'Changed'  

         ELSE 'No Change'  

     END AS ChangeStatus  

 FROM Customers c  

 LEFT JOIN PreviousCustomers pc ON c.CustomerID = pc.CustomerID;  

            
        

Create SQL query with SqlQueryBuilder 4

            
var (sql4, parameters4) = new SqlQueryBuilder()  

    .WithCTETable(new Table("PreviousCustomers"), new SqlQueryBuilder()  

        .Select()  

        .Columns("CustomerID","CompanyName","ContactName")  

        .From("Customers")  

        .Where(new Where(new IN(new Column("CustomerID"), "ALFKI", "ANATR", "ANTON", "AROUT", "BERGS")))  

    )  

    .Select()  

    .Column("c.CustomerID", "CustomerID")  

    .Column(new CASE().When(new BINARY_CHECKSUM(new Column("c.CompanyName"), new Column("c.ContactName"))  

        .NotEqualeTo(new BINARY_CHECKSUM(new Column("pc.CompanyName"), new Column("pc.ContactName"))))  

        .Then("Changed")  

        .Else("No Change")  

    , "ChangeStatus")  

    .From("Customers","c")  

    .Join(new List<IJoin>()   

    {  

        new LEFTJOIN().TableName("PreviousCustomers","pc")  

            .On(new Column("c.CustomerID").Equale(new Column("pc.CustomerID")))  

    })  

    .Build();  

            
        

Query build by SqlQueryBuilder 4

            
WITH PreviousCustomers
AS (SELECT CustomerID,
           CompanyName,
           ContactName
    FROM Customers
    WHERE CustomerID IN (@pMAIN_2507200155149967330, @pMAIN_2507200155149967331, @pMAIN_2507200155149967332, @pMAIN_2507200155149967333, @pMAIN_2507200155149967334))
SELECT c.CustomerID AS CustomerID,
       CASE WHEN BINARY_CHECKSUM(c.CompanyName, c.ContactName) <> BINARY_CHECKSUM(pc.CompanyName, pc.ContactName) THEN @pMAIN_2507200155149967335 ELSE @pMAIN_2507200155149967336 END AS ChangeStatus
FROM Customers AS c
     LEFT OUTER JOIN
     PreviousCustomers AS pc
     ON c.CustomerID = pc.CustomerID;


            
        

Parameters (If used)

Query Results 4: