Skip to content

Instantly share code, notes, and snippets.

@xiaom
Last active December 19, 2015 11:09
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save xiaom/5945746 to your computer and use it in GitHub Desktop.
Save xiaom/5945746 to your computer and use it in GitHub Desktop.
compare two sql statement
DECLARE @Query1Checksum bigint
DECLARE @Query2Checksum bigint
Use Northwind354 
-- Get checksum from source query
Select @Query1Checksum = CHECKSUM_AGG(BINARY_CHECKSUM(*))
FROM
(
-- [Start Source Query]
SELECT O.OrderID, O.OrderDate,
(SELECT MAX(OrdDet.UnitPrice)
FROM [Order Details] OrdDet
WHERE O.OrderID = OrdDet.OrderID) AS MaxUnitPrice
FROM Orders O
-- [End Source Query]
)
AS Source
 
-- Get checksum from comparison query
Select @Query2Checksum = CHECKSUM_AGG(BINARY_CHECKSUM(*))
FROM
(
-- [Start Comparison Query]
SELECT DISTINCT O.OrderID, O.OrderDate, OD.UnitPrice
FROM Orders AS O INNER JOIN [Order Details] AS OD
ON O.OrderID = OD.OrderID
WHERE OD.UnitPrice = (
SELECT MAX(OD2.UnitPrice) FROM [Order Details]AS OD2
WHERE OD2.OrderID = O.OrderID
)
-- [End Comparison Query]
)
As Comparison
 
-- Compare CheckSums to determine equality
IF @Query1Checksum = @Query2Checksum
PRINT 'Queries are Equal'
ELSE
PRINT 'Queries are NOT Equal'
--- subprocess.call("sqlcmd -S cypress.csil.sfu.ca -i test.sql -o test.result")
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment