While working with T-SQL, many times we create complex queries, and then we might need to optimize them at some later point of time to reduce the execution time. This activity always demands an approach to compare data for the older and newer version of the T-SQL. And here comes the question – how do we validate the data of both queries in a way which is reliable, quick & simple?
There are many ways to do this and here, I will be explaining a way which I find myself using a lot of times.
Before diving deeper into the approach, let’s first understand the below two functions of SQL which I will be using in my approach:
CHECKSUM() – calculates a hash for one or more column values in a row and returns an integer. This turn value is deterministic in nature; meaning- will always return the same output for the same input values.
CHECKSUM_AGG() – is an aggregate function that takes a single integer value from multiple rows and calculates an aggregated checksum for each group.
So, to match the data and check if there is any discrepancy, we need to follow the below steps:
Let’s see how to implement the above steps:
Here, we have created a view named “vw_innerwithwhere” using the below query:
CREATE VIEW vw_innerwithwhere AS SELECT * FROM [dbo].[tblEpisode] E INNER JOIN [dbo].[tblEpisodeEnemy] EE ON E.EpisodeId = EE.EpisodeId WHERE EE.EpisodeId IN (1,2,3,4,6,8)
Now, we re-write this query by removing WHERE condition and applying the condition in INNER JOIN and we create a new view named “vw_innerwithcondition” just to test whether both the query results the same output or not.
CREATE VIEW vw_innerwithcondition AS SELECT * FROM [dbo].[tblEpisode] E INNER JOIN [dbo].[tblEpisodeEnemy] EE ON E.EpisodeId = EE.EpisodeId AND EE.EpisodeId IN (1,2,3,4,6,8)
Now, let’s test whether both the views return the same output or not by using CHECKSUM() and CHECKSUM_AGG().
Please note that for best precision and validation, make sure we include the Primary Key column, as the value of the primary key will always be unique and will always generate a unique CHECKSUM(), i.e., with more accuracy.
Let’s do it practically and see how to validate whether the data is the same or not in 2 different views.
Now, we have modified the view “vw_innerwithcondition” by manipulating the condition as below:
ALTER VIEW vw_innerwithcondition AS SELECT * FROM [dbo].[tblEpisode] E INNER JOIN [dbo].[tblEpisodeEnemy] EE ON E.EpisodeId = EE.EpisodeId AND EE.EpisodeId IN (1,2,3,4,6,9)
Now we follow the following process to validate the data:
In this way, we can test if there is any discrepancy between two queries in SQL with less manual work and in less time.
© All Rights Reserved. Inkey IT Solutions Pvt. Ltd. 2023