This is a post that looks at how to compare data changes in recent data. A customer recently asked me about looking at a table, and choosing specific data to compare. In this case, the data they were looking to compare was the most recent data.
Scenario
I decided to set up a quick scenario to showcase this for the customer. I created a table that has some data:
CREATE TABLE [dbo].[DataWithTime](
[myid] [int] IDENTITY(1,1) NOT NULL,
[Mydata] [varchar](20) NULL,
[mytime] [datetime] NULL,
CONSTRAINT [DataWithTimePK] PRIMARY KEY CLUSTERED
(
[myid] ASC
)
GO
INSERT INTO dbo.DataWithTime (Mydata, mytime)
VALUES
( 'A', N'2021-01-22T12:42:33.213' ),
( 'B', N'2021-01-22T12:52:33.213' ),
( 'C', N'2021-01-22T13:02:33.213' ),
( 'D', N'2021-01-22T13:07:33.213' ),
( 'E', N'2021-01-22T13:12:33.213' )
I put this in my sandbox database. I wanted a second copy of this same table, but with less data, in another database. I edited the insert statement to look like this:
INSERT INTO dbo.DataWithTime (Mydata, mytime)
VALUES
( 'A', N'2021-01-20T12:42:33.213' ),
( 'B', N'2021-01-21T12:52:33.213' ),
( 'CC', N'2021-01-23T13:02:33.213' ),
( 'D', N'2021-01-24T13:07:33.213' ),
( 'EE', N'2021-01-25T13:12:33.213' ),
( 'F', N'2021-01-26T13:07:33.213' )
Now I have two copies of my table, with disparate data. What’s different?
Data Comparison Filters
If I open SQL Data Compare, you get the default comparison. I’ll set this up with my two test tables:
When I do the comparison, I see the differences between the tables. As you can see, I edited two rows and added one.
That’s great, and in a table of a few rows, this isn’t an issue. What if this table has a million rows? Or a billion? I don’t want to scan everything.I want to limit things.
I can, if I click “Edit Project”.
and then choose Tables and Views. I’ll see my table listed.
I can select the row with my table, DataWithTime, and then I can click the “Where clause” link in the upper right.
This pops up a dialog where I can enter a WHERE clause to be used for the table. I can set the same clause for both the source and target, or use separate ones. I’ll use the same one here.
I can click OK for this and then Compare now to re-run the project. This gives me the data compared, but without looking at any data before the 25th of Jan. Notice only two rows below instead of 3.
Am I sure this still didn’t impact my SQL Server with a large query? This works great with 5 rows, but what about 1billion? Well, I ran the XEvent Profiler while I was editing the project, and then filtered this down to the SQL tools. When I do that, I see this:
The query being issued has my WHERE clause, which filters out data at the query processing level. This doesn’t guarantee a seek or limited reads, but if I have the column indexed, then I would get an efficient a plan as I could get.
SQL Data Compare is fantastic tool for finding data differences. Comparing large volumes of data can be slow, but if you use filters, you can dramatically speed things up. If you haven’t tried SQL Data Compare, download an evaluation today and see what you think.