T-SQL Tuesday #114 Puzzle Party

TSQL2SDAY

This months T-SQL Tuesday comes from Matthew McGiffen who has given us the topic of “Puzzles”, thank you for a great topic!

Puzzles can be fun or frustrating, but I like them because it can be a great way to showcase who you are and how you work. For example, I was given a puzzle from a company that was interested in me but wanted to know how I approached specific problems. They gave me a simple SQL query and three word problems to go along with it. My reply back illustrated how I approach problems, document findings, performance test, and also work to obtain business requirements.

I gave them back a long paper that broke down the primary problem into nine different scenarios for how to interpret their question and I wrote a total of 35 queries spanning all nine scenarios to pick the best option. This included a procedure they could run to test these scenarios and see my work.

You can find the procedure here on my github, you will need to alter line 56 with the options 1-9 to test a scenario.

Scenario:

Imagine that you are given two versions of a table.  The number, types, and names of the columns haven’t changed between the two versions.  There are more records in the new version of the table.

SELECT id1 as oid1, id2 as oid2, COUNT(*) oc
INTO #old
FROM OriginalData
GROUP BY id1, id2

SELECT id1 as nid1, id2 as nid2, COUNT(*) nc
INTO #new
FROM ReplacementData
GROUP BY id1, id2

SELECT *
INTO #linkRaw
FROM #old old OUTER JOIN #new new
	ON oid1 = nid1
		AND oid2 = nid2

List of Questions

  • Question 1 –
    Create a new table that contains all the new records that are only found in the replacementdata.  Be conscious of any assumptions that you make.  What are the positives and negatives to your solution?
  • Question 2 –
    Explain the following statement
    SUM(case when isnull(old.field1, ”) = isnull(new.field1, ”) THEN 1 ELSE 0 END)
  • Question 3-
    Write a query that tests if the records that exist in both versions of the tables are different between the two versions. Does your query let you know which field changed? How many records changed?

Question 1

Create a new table that contains all the new records that are only found in the replacementdata.  Be conscious of any assumptions that you make.  What are the positives and negatives to your solution?

The most uttered phrase I’ve ever used is “it depends”. My second most used phrase is “business requirements should drive development, development should not drive business requirements.” Looking at the request, I have generated a list of questions I would want to clarify a few specifics to ensure I’m giving back the correct results within context.

While this problem is simple in nature to solve, solving the problem WELL is more complex. Because of those complexities, I need to create a full list of business requirements so it will drive my development. The idea by laying the architecture and philosophical questions out first, we can have a hardened road map that streamlines the development and QA process. And again because of the complexity of this to do WELL, my solutions “will depend” on the requirements.

I have come up with nine scenarios from this example that each has a ranging number of options to pick. At the end, I will have a full code section that will let you alter line 56 with the value range of 1-9 letting you see every example I created.

Here was my SQL Server version I ran for performance testing: (Performance results will vary based on SQL version and the optimizer trace flag configuration/compatibility level of the database.) I will summarize them for you so you do not need to see the work involved but it will be at the end of this post.

Microsoft SQL Server 2014 – 12.0.2000.8 (X64) Feb 20 2014 20:04:26 Copyright (c) Microsoft Corporation Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)

First, let’s deal with the questions and ideas I wrestled with:

create a new table that contains all the new records that are only found in the replacementdata.

  • What constitutes a “record”?
    • Are ID1 / ID2 individual records?              
      • Because the values are used in combination to create a join, I would assume those values are typically the clustered index on the tables (not necessarily the primary key). And since the combination of those records is used to create a single row in the #new or #old table, a row is typically identified as a record. So I will not treat id1 and id2 as individual records, but I would want to clarify with my stakeholder so we can set expectations and deliver correct results.
    • Is the combination of ID1 / ID2 a record?
    • Is the combination of ID1 / ID2 / Aggregate Count a record?
  • What are the constraints of the table and the integrity handling techniques for the data?
    • Can I have a NULL inserted in the ID1 / ID2 column?
    • Can I have a blank value inserted in those columns?
    • Can I have both a blank or a NULL in either of the columns?
    • Is it a bug if there is a NULL / blank in either column or does that row still count as a record if one of the columns has a value?
    • Are there also default values that need to be taken into account? Some systems do not use NULL or blank and instead rely on default values to indicate the absence of known values.
  • What are business use case scenarios I need to test and validate?
    • Are there procedural testings/data integrity testing that I need to have done prior, during, or after these statements?
  • How do I handle performance?
    • How many records are there?
    • Do I need to worry about locking/blocking?
    • Is this on production and should I enable MAXDOP or Resource Governor?
    • Should we convert to variable tables for minimal statistic creation or is this a semi-frequent event that should reside in staging tables?
      • For the purpose of this particular demonstration, I am doing to use staging tables instead of the temp table example given. There is a neat feature in SQL Server where you cannot create the same temp table in the same batch execution. So rather than add additional complexity for this questionnaire, I’m going to roll with the staging tables.
    • Do we need to apply indexes / should we apply indexes?

That’s a lot of questions. Here’s my take on them:

Because the data inserted into #old and #new is aggregate data, we can have duplicate rows for columns ID1 and ID2 between tables ReplacementData and OriginalData. (It may also be possible that we have X values in OriginalData but not in ReplacementData and it is definitely true that we have A values in ReplacementData and B values in OriginalData.)

Based on the SQL provided, we at least know between ID1, ID2, and the aggregate count that we cannot have duplicate rows in #old or #new because it is being grouped. The second indicator there is the SELECT INTO method which means we are creating a brand new table on the fly to insert those records, so each row should be unique among each table.

Now, philosophical differences and feelings aside for NULL, I need to take them into account or at least be aware of their impact. NULL is the unknown, you can not compare something to nothing as it is the absence of value.  Different operators in SQL handle this differently.

Ok Aristotle, let’s stop thinking about work and actually do some work:

Scenarios 1 thru 3 deal with treating ID1 and ID2 as a record. If that combination exists in ReplacementData and not OriginalData, this will retrieve that information.

My ideal solution for scenarios 1 thru 3 would be:

SELECT nid1, nid2
	FROM new n
	OUTER APPLY (
		SELECT o.oid1, o.oid2
		FROM old o
		WHERE ISNULL(o.oid1, '') = ISNULL(n.nid1, '')
			AND ISNULL(o.oid2, '') = ISNULL(n.nid2, '')
		) a
	WHERE a.oid1 IS NULL
		AND a.oid2 IS NULL
		AND (
			ISNULL(a.oid1, '') <> ISNULL(n.nid1, '')
			OR ISNULL(a.oid2, '') <> ISNULL(n.nid2, '')
			);

This is the fastest performing query with the current information available to me that best resolves and applies to scenarios 1 – 3. It handles nulls, blank values, and default values. It doesn’t matter if we have more records in the old table or not. The problem is that this is more complex and difficult to maintain.

Scenario 1

Scenario 2

Scenario 3

Scenarios 4 thru 6 deal with treating ID1, ID2, and the aggregate as a record. If that combination exists in #new and not #old, we are considering it a record that did not exist in the OriginalData table. (Example: I visit the movie theater twice, the old DB has one entry for this and the new DB has two entries for this. This is shows that while a record may exist in OriginalData, it is not showing the complete record and the complete or real record that exists is in ReplacementData. This is the data that is pulled back.)

My ideal solution for scenarios 4 thru 6 would be:

SELECT n.nid1, n.nid2, n.nc
	FROM new n
	WHERE NOT EXISTS (
			SELECT 1
			FROM old o
			WHERE ISNULL(o.oid1, '') = ISNULL(n.nid1, '')
				AND ISNULL(o.oid2, '') = ISNULL(n.nid2, '')
				AND o.oc = n.nc
			);

This is the fastest performing query with the current information available to me that best resolves and applies to scenarios 4 – 6. It handles nulls, blank values, and default values. It doesn’t matter if we have more records in the old table or not.

Scenario 4

Scenario 5

Scenario 6

Scenarios 7 thru 9 deal with treating ID1 and ID2 as a record and even if an entry is a duplicate, it is still considered a unique entry. (Example: I visit the movie theater twice, the old DB has one entry for this and the new DB has two entries for this. What will come back is a single entry for me saying I went to a movie. This is because there are two records in the ReplacementData table and one record in the OriginalData table, so the record coming back is the difference between the two, showing it only exists in ReplacementData.)

My ideal solution for scenarios 7 thru 9 would be:

SELECT nid1, nid2, CASE 
			WHEN (a.oc < n.nc)
				THEN (n.nc - a.oc)
			ELSE n.nc
			END AS nc
	FROM new n
	OUTER APPLY (
		SELECT o.oid1, o.oid2, o.oc
		FROM old o
		WHERE isnull(o.oid1, '') = isnull(n.nid1, '')
			AND isnull(o.oid2, '') = isnull(n.nid2, '')
		) a
	WHERE (
			a.oid1 IS NULL
			AND a.oid2 IS NULL
			)
		AND isnull(a.oc, '') <> isnull(n.nc, '')
		OR a.oc < n.nc;

This is the fastest performing query with the current information available to me that best resolves and applies to scenarios 7 – 9. It handles nulls, blank values, and default values. It doesn’t matter if we have more records in the old table or not. The problem is that this is more complex and difficult to maintain.

Scenario 7

Scenario 8

Scenario 9

Now real briefly before I move onto the next question, I wanted to touch base on performance. I’m going to quickly summarize some techniques and things I’m keeping in mind:

  • Coalesce for when NULL logic is not heavily based in the where or join clause.
  • Attempt to keep queries in set-based logic vs row by agonizing row.
    • See correlated subqueries. (There are tricks to getting this to function very well though with apply where you can short circuit the optimizer.)
  • Only reference rows necessary.
  • Keep logic as simple as possible.
  • The right tool for the right job. I have multiple queries that operate differently and depending on the circumstance, one of those may actually be a better fit. The best method of tuning is always to monitor, measure historical performance, adjust, try, prove, and instantiate.

Back to list of Questions

Question 2

Explain the following statement

SUM(case when isnull(old.field1, '') = isnull(new.field1, '') THEN 1 ELSE 0 END)

If Field1 from the table old is null, then it should be substituted to a value of blank. Likewise, we are also in parallel analyzing when field1 from the new table is null, we are also making that value be blank. From there, we are saying, when the field1 matches between the old and new table, place a 1 indicating a match or a 0 indicating no match. (The isnull piece is necessary here as I mentioned before, you cannot compare a NULL.) This creates a count for us that is summarized in an aggregate to tell us the number of rows that match between tables taking into account NULL.

Back to list of Questions

Question 3

Write a query that tests if the records that exist in both versions of the tables are different between the two versions. Does your query let you know which field changed? How many records changed?

This question goes back to me needing to acquire the proper business requirements, most of my questions initially still apply.

The problem is that the aggregate data does not come with a foreign or primary key column that is inserted into new or old. So while I can link back to ReplacementData or OriginalData, I don’t have any identifier telling me which row it was. So I would not be able to identify without modifying the SQL presented to me which individual rows had changed, but I could link back and say ID1 and ID2 have different quantities between the tables and I can illustrate how many are different and which are different.

My final query from question 1 should illicit this result. Review Scenario 9 for the full explanation, I feel this is what was trying to be achieved.

Back to list of Questions

Scenario 1

Goal: We have rows in new and old, we want to know what records exist in ReplacementData but not in OriginalData.

Criteria:

– All records that exist in OriginalData will exist in ReplacementData.

– ReplacementData will have more records than the OriginalData table.

– There are no default values, blank values, or NULL that exist in OriginalData or ReplacementData.

Method:

Matching oid1 to nid1 and oid2 to nid2 between the old and new and ignoring the aggregate to find out what only exists in ReplacementData.

Expected Result Set from data input:

ID1ID2
32

Performance Testing Query 1:

SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SET STATISTICS XML ON;
	
-- Query 1
	SELECT n.nid1, n.nid2
	FROM new n
	LEFT JOIN old o ON o.oid1 = n.nid1
		AND o.oid2 = n.nid2
	WHERE o.oid1 IS NULL
		AND o.oid2 IS NULL;

Table ‘old’. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.


Table ‘new’. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.


 SQL Server Execution Times:
CPU time = 0 ms,  elapsed time = 345 ms.

Performance Testing Query 2:

SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SET STATISTICS XML ON;
	
-- Query 2
SELECT n.nid1, n.nid2
	FROM new n
	WHERE NOT EXISTS (
			SELECT 1
			FROM old o
			WHERE o.oid1 = n.nid1
				AND o.oid2 = n.nid2
			);

Table ‘old’. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.


Table ‘new’. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.


SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 67 ms.

Performance Testing Query 3:

SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SET STATISTICS XML ON;
	
-- Query 2
-- Query 3
SELECT nid1, nid2
FROM new
WHERE nid1 NOT IN (
		SELECT oid1
		FROM old
		)
	AND nid2 NOT IN (
		SELECT oid2
		FROM old
		);

Table ‘old’. Scan count 2, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.


Table ‘new’. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.


SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 45 ms.

Back to scenarios 1 – 3

SCENARIO 2

Goal: We have rows in new and old, we want to know what records exist in ReplacementData but not in OriginalData.

Criteria:

– All records that exist in OriginalData will exist in ReplacementData.

– ReplacementData will have more records than the OriginalData table.

– There are default values, blank values, or NULL that exist in OriginalData or ReplacementData.

Method: Matching oid1 to nid1 and oid2 to nid2 between the old and new and ignoring the aggregate to find out what only exists in ReplacementData.

ID1ID2
3
2
NULL 2

Performance Testing Query 1:

Performance Testing:
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SET STATISTICS XML ON;

	-- Query 1
	SELECT n.nid1, n.nid2
	FROM new n
	LEFT JOIN old o ON ISNULL(o.oid1, '') = ISNULL(n.nid1, '')
		AND ISNULL(o.oid2, '') = ISNULL(n.nid2, '')
	WHERE o.oid1 IS NULL
		AND o.oid2 IS NULL
		AND (
			ISNULL(o.oid1, '') <> ISNULL(n.nid1, '')
			OR ISNULL(o.oid2, '') <> ISNULL(n.nid2, '')
			);

Table ‘old’. Scan count 1, logical reads 6, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.


Table ‘new’. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.


SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 208 ms.

Performance Testing Query 2:

SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SET STATISTICS XML ON;

-- Query 2
	SELECT n.nid1, n.nid2
	FROM new n
	WHERE NOT EXISTS (
			SELECT 1
			FROM old o
			WHERE ISNULL(o.oid1, '') = ISNULL(n.nid1, '')
				AND ISNULL(o.oid2, '') = ISNULL(n.nid2, '')
			);

Table ‘old’. Scan count 1, logical reads 6, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.


Table ‘new’. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.


SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 125 ms.

Performance Testing Query 3:

SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SET STATISTICS XML ON;

-- Query 3
	SELECT nid1, nid2
	FROM new
	
	EXCEPT
	
	SELECT oid1, oid2
	FROM old;

Table ‘old’. Scan count 1, logical reads 6, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.


Table ‘new’. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.


SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 83 ms.

Performance Testing Query 4:

SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SET STATISTICS XML ON;

-- Query 4
	SELECT nid1, nid2
	FROM new n
	OUTER APPLY (
		SELECT o.oid1, o.oid2
		FROM old o
		WHERE ISNULL(o.oid1, '') = ISNULL(n.nid1, '')
			AND ISNULL(o.oid2, '') = ISNULL(n.nid2, '')
		) a
	WHERE a.oid1 IS NULL
		AND a.oid2 IS NULL
		AND (
			ISNULL(a.oid1, '') <> ISNULL(n.nid1, '')
			OR ISNULL(a.oid2, '') <> ISNULL(n.nid2, '')
			);

Table ‘old’. Scan count 1, logical reads 6, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.


Table ‘new’. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.


SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 146 ms.

Back to scenarios 1 – 3

SCENARIO 3

Goal: We have rows in new and old, we want to know what records exist in ReplacementData but not in OriginalData.

Criteria:

– Not all records that exist in OriginalData will exist in ReplacementData.

– ReplacementData will have more records than the OriginalData table.

– There are default values, blank values, or NULL that exist in OriginalData or ReplacementData.

Method: Matching oid1 to nid1 and oid2 to nid2 between the old and new and ignoring the aggregate to find out what only exists in ReplacementData.

ID1ID2
32
NULL2

Performance Testing Query 1:

SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SET STATISTICS XML ON;

	-- Query 1
	SELECT n.nid1, n.nid2
	FROM new n
	LEFT JOIN old o ON ISNULL(o.oid1, '') = ISNULL(n.nid1, '')
		AND ISNULL(o.oid2, '') = ISNULL(n.nid2, '')
	WHERE o.oid1 IS NULL
		AND o.oid2 IS NULL
		AND (
			ISNULL(o.oid1, '') <> ISNULL(n.nid1, '')
			OR ISNULL(o.oid2, '') <> ISNULL(n.nid2, '')
			);

Table ‘old’. Scan count 1, logical reads 6, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.


Table ‘new’. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.


SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 175 ms.

Performance Testing Query 2:

SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SET STATISTICS XML ON;

-- Query 2
	SELECT n.nid1, n.nid2
	FROM new n
	WHERE NOT EXISTS (
			SELECT 1
			FROM old o
			WHERE ISNULL(o.oid1, '') = ISNULL(n.nid1, '')
				AND ISNULL(o.oid2, '') = ISNULL(n.nid2, '')
			);

Table ‘old’. Scan count 1, logical reads 6, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.


Table ‘new’. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.


SQL Server Execution Times:
CPU time = 16 ms, elapsed time = 143 ms.

Performance Testing Query 3:

SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SET STATISTICS XML ON;

-- Query 3
	SELECT nid1, nid2
	FROM new
	
	EXCEPT
	
	SELECT oid1, oid2
	FROM old;

Table ‘old’. Scan count 1, logical reads 6, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.


Table ‘new’. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.


SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 130 ms.

Performance Testing Query 4:

SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SET STATISTICS XML ON;

-- Query 4
	SELECT nid1, nid2
	FROM new n
	OUTER APPLY (
		SELECT o.oid1, o.oid2
		FROM old o
		WHERE ISNULL(o.oid1, '') = ISNULL(n.nid1, '')
			AND ISNULL(o.oid2, '') = ISNULL(n.nid2, '')
		) a
	WHERE a.oid1 IS NULL
		AND a.oid2 IS NULL
		AND (
			ISNULL(a.oid1, '') <> ISNULL(n.nid1, '')
			OR ISNULL(a.oid2, '') <> ISNULL(n.nid2, '')
			);

Table ‘old’. Scan count 1, logical reads 6, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.


Table ‘new’. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.


SQL Server Execution Times:
CPU time = 15 ms, elapsed time = 51 ms.

Back to scenarios 1 – 3

Scenario 4

Goal: We have rows in new and old, we want to know what records exist in ReplacementData but not in OriginalData.

Criteria:

– All records that exist in OriginalData will exist in ReplacementData.

– ReplacementData will have more records than the OriginalData table.

– There are no default values, blank values, or NULL that exist in OriginalData or ReplacementData.

Method: Matching oid1 to nid1, oid2 to nid2, and the count of records found between the old and new find what only exists in ReplacementData.

Expected Result Set from data input:

ID1ID2nc
113
321

Performance Testing Query 1:

SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SET STATISTICS XML ON;

-- Query 1
	SELECT n.nid1, n.nid2, n.nc
	FROM new n
	LEFT JOIN old o ON o.oid1 = n.nid1
		AND o.oid2 = n.nid2
		AND n.nc = o.oc
	WHERE o.oid1 IS NULL
		AND o.oid2 IS NULL
		AND o.oc IS NULL;

Table ‘old’. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.


Table ‘new’. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.


SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 132 ms.

Performance Testing Query 2:

SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SET STATISTICS XML ON;

-- Query 2
	SELECT n.nid1, n.nid2, n.nc
	FROM new n
	WHERE NOT EXISTS (
			SELECT 1
			FROM old o
			WHERE o.oid1 = n.nid1
				AND o.oid2 = n.nid2
				AND o.oc = n.nc
			);

Table ‘old’. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.


Table ‘new’. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.


SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 150 ms.

Performance Testing Query 3:

SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SET STATISTICS XML ON;

-- Query 3
	SELECT nid1, nid2, nc
	FROM new
	WHERE nid1 NOT IN (
			SELECT oid1
			FROM old
			)
		OR nid2 NOT IN (
			SELECT oid2
			FROM old
			)
		OR nc NOT IN (
			SELECT oc
			FROM old
			);

Table ‘old’. Scan count 6, logical reads 7, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.


Table ‘new’. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.


SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 107 ms.

Performance Testing Query 4:

SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SET STATISTICS XML ON;

-- Query 4
	SELECT nid1, nid2, nc
	FROM new
	
	EXCEPT
	
	SELECT oid1, oid2, oc
	FROM old;

Table ‘old’. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.


Table ‘new’. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.


SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 120 ms.

Performance Testing Query 5:

SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SET STATISTICS XML ON;

-- Query 5
	SELECT nid1, nid2, nc
	FROM new n
	OUTER APPLY (
		SELECT o.oid1, o.oid2, o.oc
		FROM old o
		WHERE o.oid1 = n.nid1
			AND o.oid2 = n.nid2
			AND o.oc = n.nc
		) a
	WHERE a.oid1 IS NULL
		AND a.oid2 IS NULL;

Table ‘old’. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.


Table ‘new’. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.


SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 100 ms.

Back to scenarios 4 – 6

SCENARIO 5

Goal: We have rows in new and old, we want to know what records exist in ReplacementData but not in OriginalData.

Criteria:

– All records that exist in OriginalData will exist in ReplacementData.

– ReplacementData will have more records than the OriginalData table.

– There are default values, blank values, or NULL that exist in OriginalData or ReplacementData.

Method: Matching oid1 to nid1, oid2 to nid2, and the count of records found between the old and new find what only exists in ReplacementData.

Expected Result Set from data input:

ID1ID2nc
113
321
NULL21

Performance Testing Query 1:

Performance Testing:
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SET STATISTICS XML ON;

	-- Query 1
	SELECT n.nid1, n.nid2, n.nc
	FROM new n
	LEFT JOIN old o ON ISNULL(o.oid1, '') = ISNULL(n.nid1, '')
		AND ISNULL(o.oid2, '') = ISNULL(n.nid2, '')
		AND o.oc = n.nc
	WHERE o.oid1 IS NULL
		AND o.oid2 IS NULL
		AND (
			ISNULL(o.oid1, '') <> ISNULL(n.nid1, '')
			OR ISNULL(o.oid2, '') <> ISNULL(n.nid2, '')
			);

Table ‘old’. Scan count 1, logical reads 6, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.


Table ‘new’. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.


SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 274 ms.

Performance Testing Query 2:

Performance Testing:
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SET STATISTICS XML ON;

-- Query 2
	SELECT n.nid1, n.nid2, n.nc
	FROM new n
	WHERE NOT EXISTS (
			SELECT 1
			FROM old o
			WHERE ISNULL(o.oid1, '') = ISNULL(n.nid1, '')
				AND ISNULL(o.oid2, '') = ISNULL(n.nid2, '')
				AND o.oc = n.nc
			);

Table ‘old’. Scan count 1, logical reads 6, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.


Table ‘new’. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.


SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 120 ms.

Performance Testing Query 3:

Performance Testing:
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SET STATISTICS XML ON;

-- Query 3
	SELECT nid1, nid2, nc
	FROM new
	
	EXCEPT
	
	SELECT oid1, oid2, oc
	FROM old;

Table ‘old’. Scan count 1, logical reads 6, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.


Table ‘new’. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.


SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 234 ms.

Performance Testing Query 4:

Performance Testing:
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SET STATISTICS XML ON;

-- Query 4
	SELECT nid1, nid2, nc
	FROM new n
	OUTER APPLY (
		SELECT o.oid1, o.oid2
		FROM old o
		WHERE ISNULL(o.oid1, '') = ISNULL(n.nid1, '')
			AND ISNULL(o.oid2, '') = ISNULL(n.nid2, '')
			AND o.oc = n.nc
		) a
	WHERE a.oid1 IS NULL
		AND a.oid2 IS NULL
		AND (
			ISNULL(a.oid1, '') <> ISNULL(n.nid1, '')
			OR ISNULL(a.oid2, '') <> ISNULL(n.nid2, '')
			);

Table ‘old’. Scan count 1, logical reads 6, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.


Table ‘new’. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.


SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 234 ms.

Back to scenarios 4 – 6

SCENARIO 6

Goal: We have rows in new and old, we want to know what records exist in ReplacementData but not in OriginalData.

Criteria:

– Not all records that exist in OriginalData will exist in ReplacementData.

– ReplacementData will have more records than the OriginalData table.

– There are default values, blank values, or NULL that exist in OriginalData or ReplacementData.

Method: Matching oid1 to nid1, oid2 to nid2, and the count of records found between the old and new find what only exists in ReplacementData.

Expected Result Set from data input:

ID1ID2nc
113
321
NULL21

Performance Testing Query 1:

Performance Testing:
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SET STATISTICS XML ON;

-- Query 1
	SELECT n.nid1, n.nid2, n.nc
	FROM new n
	LEFT JOIN old o ON ISNULL(o.oid1, '') = ISNULL(n.nid1, '')
		AND ISNULL(o.oid2, '') = ISNULL(n.nid2, '')
		AND o.oc = n.nc
	WHERE o.oid1 IS NULL
		AND o.oid2 IS NULL
		AND (
			ISNULL(o.oid1, '') <> ISNULL(n.nid1, '')
			OR ISNULL(o.oid2, '') <> ISNULL(n.nid2, '')
			);

Table ‘old’. Scan count 1, logical reads 6, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.


Table ‘new’. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.


SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 196 ms.

Performance Testing Query 2:

Performance Testing:
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SET STATISTICS XML ON;

-- Query 2
	SELECT n.nid1, n.nid2, n.nc
	FROM new n
	WHERE NOT EXISTS (
			SELECT 1
			FROM old o
			WHERE ISNULL(o.oid1, '') = ISNULL(n.nid1, '')
				AND ISNULL(o.oid2, '') = ISNULL(n.nid2, '')
				AND o.oc = n.nc
			);

Table ‘old’. Scan count 1, logical reads 6, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.


Table ‘new’. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.


SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 79 ms.

Performance Testing Query 3:

Performance Testing:
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SET STATISTICS XML ON;

-- Query 3
	SELECT nid1, nid2, nc
	FROM new
	
	EXCEPT
	
	SELECT oid1, oid2, oc
	FROM old;

Table ‘old’. Scan count 1, logical reads 6, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.


Table ‘new’. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.


SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 107 ms.

Performance Testing Query 4:

Performance Testing:
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SET STATISTICS XML ON;

-- Query 4
	SELECT nid1, nid2, nc
	FROM new n
	OUTER APPLY (
		SELECT o.oid1, o.oid2
		FROM old o
		WHERE ISNULL(o.oid1, '') = ISNULL(n.nid1, '')
			AND ISNULL(o.oid2, '') = ISNULL(n.nid2, '')
			AND o.oc = n.nc
		) a
	WHERE a.oid1 IS NULL
		AND a.oid2 IS NULL
		AND (
			ISNULL(a.oid1, '') <> ISNULL(n.nid1, '')
			OR ISNULL(a.oid2, '') <> ISNULL(n.nid2, '')
			);

Table ‘old’. Scan count 1, logical reads 6, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.


Table ‘new’. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.


SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 107 ms.

Back to scenarios 4 – 6

SCENARIO 7

Goal: We have rows in new and old, we want to know what records exist in ReplacementData but not in OriginalData.

Criteria:

– All records that exist in OriginalData will exist in ReplacementData.

– ReplacementData will have more records than the OriginalData table.

– There are no default values, blank values, or NULL that exist in OriginalData or ReplacementData.

Method: Matching oid1 to nid1 and oid2 to nid2 between the old and new. When I have found matches, I need to compare the aggregate count. We are treating a record as an entry into ReplacementData of ID1 and ID2. If we had two counts of this record in ReplacementData and one count of this record in OriginalData, I would say the ReplacementData table has one additional record of ID1 and ID2 than the OriginalData table had.

Expected Result Set from data input:

ID1ID2nc
111
321

Performance Testing Query 1:

Performance Testing:
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SET STATISTICS XML ON;

-- Query 1
	SELECT n.nid1, n.nid2, CASE 
			WHEN (o.oc < n.nc)
				THEN (n.nc - o.oc)
			ELSE n.nc
			END AS nc
	FROM new n
	LEFT JOIN old o ON o.oid1 = n.nid1
		AND o.oid2 = n.nid2
	WHERE (
			o.oid1 IS NULL
			AND o.oid2 IS NULL
			)
		OR o.oc < n.nc;

Table ‘old’. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.


Table ‘new’. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.


SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 259 ms.

Performance Testing Query 2:

Performance Testing:
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SET STATISTICS XML ON;

--	Query 2
	SELECT n.nid1, n.nid2, n.nc
	FROM new n
	LEFT JOIN old o ON o.oid1 = n.nid1
		AND o.oid2 = n.nid2
	WHERE o.oid1 IS NULL
		AND o.oid2 IS NULL
	
	UNION
	
	SELECT n.nid1, n.nid2, (n.nc - o.oc) AS nc
	FROM new n
	INNER JOIN old o ON o.oid1 = n.nid1
		AND o.oid2 = n.nid2
	WHERE o.oc < n.nc;

Table ‘Workfile’. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.


Table ‘Worktable’. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.


Table ‘new’. Scan count 2, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.


Table ‘old’. Scan count 2, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.


SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 204 ms.

Performance Testing Query 3:

Performance Testing:
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SET STATISTICS XML ON;

-- Query 3
	SELECT nid1, nid2, CASE 
			WHEN (a.oc < n.nc)
				THEN (n.nc - a.oc)
			ELSE n.nc
			END AS nc
	FROM new n
	OUTER APPLY (
		SELECT o.oid1, o.oid2, o.oc
		FROM old o
		WHERE o.oid1 = n.nid1
			AND o.oid2 = n.nid2
		) a
	WHERE (
			a.oid1 IS NULL
			AND a.oid2 IS NULL
			)
		OR a.oc < n.nc;

Table ‘old’. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.


Table ‘new’. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.


SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 56 ms.

Back to scenarios 7 – 9

SCENARIO 8

Goal: We have rows in new and old, we want to know what records exist in ReplacementData but not in OriginalData.

Criteria:

– All records that exist in OriginalData will exist in ReplacementData.

– ReplacementData will have more records than the OriginalData table.

– There are default values, blank values, or NULL that exist in OriginalData or ReplacementData.

Method: Matching oid1 to nid1 and oid2 to nid2 between the old and new. When I have found matches, I need to compare the aggregate count. We are treating a record as an entry into ReplacementData of ID1 and ID2. If we had two counts of this record in ReplacementData and one count of this record in OriginalData, I would say the ReplacementData table has one additional record of ID1 and ID2 than the OriginalData table had.

Expected Result Set from data input:

ID1ID2nc
111
321
NULL21

Performance Testing Query 1:

Performance Testing:
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SET STATISTICS XML ON;

-- Query 1
	SELECT n.nid1, n.nid2, CASE 
			WHEN (o.oc < n.nc)
				THEN (n.nc - o.oc)
			ELSE n.nc
			END AS nc
	FROM new n
	LEFT JOIN old o ON ISNULL(o.oid1, '') = isnull(n.nid1, '')
		AND isnull(o.oid2, '') = isnull(n.nid2, '')
	WHERE (
			o.oid1 IS NULL
			AND o.oid2 IS NULL
			)
		AND isnull(o.oc, '') <> isnull(n.nc, '')
		OR o.oc < n.nc;

Table ‘old’. Scan count 1, logical reads 6, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.


Table ‘new’. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.


SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 349 ms.

Performance Testing Query 2:

Performance Testing:
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SET STATISTICS XML ON;

--	Query 2
	SELECT n.nid1, n.nid2, n.nc
	FROM new n
	LEFT JOIN old o ON ISNULL(o.oid1, '') = isnull(n.nid1, '')
		AND isnull(o.oid2, '') = isnull(n.nid2, '')
	WHERE o.oid1 IS NULL
		AND o.oid2 IS NULL
		AND isnull(o.oc, '') <> isnull(n.nc, '')
	
	UNION
	
	SELECT n.nid1, n.nid2, (n.nc - o.oc) AS nc
	FROM new n
	INNER JOIN old o ON o.oid1 = n.nid1
		AND o.oid2 = n.nid2
	WHERE o.oc < n.nc;

Table ‘Workfile’. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.


Table ‘Worktable’. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.


Table ‘new’. Scan count 2, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.


Table ‘old’. Scan count 2, logical reads 7, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.


SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 45 ms.

Performance Testing Query 3:

Performance Testing:
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SET STATISTICS XML ON;

-- Query 3
	SELECT nid1, nid2, CASE 
			WHEN (a.oc < n.nc)
				THEN (n.nc - a.oc)
			ELSE n.nc
			END AS nc
	FROM new n
	OUTER APPLY (
		SELECT o.oid1, o.oid2, o.oc
		FROM old o
		WHERE isnull(o.oid1, '') = isnull(n.nid1, '')
			AND isnull(o.oid2, '') = isnull(n.nid2, '')
		) a
	WHERE (
			a.oid1 IS NULL
			AND a.oid2 IS NULL
			)
		AND isnull(a.oc, '') <> isnull(n.nc, '')
		OR a.oc < n.nc;

(3 row(s) affected)
Table ‘old’. Scan count 1, logical reads 6, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.


Table ‘new’. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.


(1 row(s) affected)
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 59 ms.

Back to scenarios 7 – 9

SCENARIO 9

Goal: We have rows in new and old, we want to know what records exist in ReplacementData but not in OriginalData.

Criteria:

– Not all records that exist in OriginalData will exist in ReplacementData.

– ReplacementData will have more records in that table than in the OriginalData table.

– There are default values, blank values, or NULL that exist in OriginalData or ReplacementData.

Method: Matching oid1 to nid1 and oid2 to nid2 between the old and new. When I have found matches, I need to compare the aggregate count. We are treating a record as an entry into ReplacementData of ID1 and ID2. If we had two counts of this record in ReplacementData and one count of this record in OriginalData, I would say the ReplacementData table has one additional record of ID1 and ID2 than the OriginalData table had.

Expected Result Set from data input:

ID1ID2nc
111
321
NULL21

Performance Testing Query 1:

Performance Testing:
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SET STATISTICS XML ON;

-- Query 1
	SELECT n.nid1, n.nid2, CASE 
			WHEN (o.oc < n.nc)
				THEN (n.nc - o.oc)
			ELSE n.nc
			END AS nc
	FROM new n
	LEFT JOIN old o ON ISNULL(o.oid1, '') = isnull(n.nid1, '')
		AND isnull(o.oid2, '') = isnull(n.nid2, '')
	WHERE (
			o.oid1 IS NULL
			AND o.oid2 IS NULL
			)
		AND isnull(o.oc, '') <> isnull(n.nc, '')
		OR o.oc < n.nc;

Table ‘old’. Scan count 1, logical reads 6, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.


Table ‘new’. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.


SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 218 ms.

Performance Testing Query 2:

Performance Testing:
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SET STATISTICS XML ON;

--	Query 2
	SELECT n.nid1, n.nid2, n.nc
	FROM new n
	LEFT JOIN old o ON ISNULL(o.oid1, '') = isnull(n.nid1, '')
		AND isnull(o.oid2, '') = isnull(n.nid2, '')
	WHERE o.oid1 IS NULL
		AND o.oid2 IS NULL
		AND isnull(o.oc, '') <> isnull(n.nc, '')
	
	UNION
	
	SELECT n.nid1, n.nid2, (n.nc - o.oc) AS nc
	FROM new n
	INNER JOIN old o ON o.oid1 = n.nid1
		AND o.oid2 = n.nid2
	WHERE o.oc < n.nc;

Table ‘Workfile’. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.


Table ‘Worktable’. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.


Table ‘new’. Scan count 2, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.


Table ‘old’. Scan count 2, logical reads 7, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.


SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 228 ms.

Performance Testing Query 3:

Performance Testing:
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SET STATISTICS XML ON;

-- Query 3
	SELECT nid1, nid2, CASE 
			WHEN (a.oc < n.nc)
				THEN (n.nc - a.oc)
			ELSE n.nc
			END AS nc
	FROM new n
	OUTER APPLY (
		SELECT o.oid1, o.oid2, o.oc
		FROM old o
		WHERE isnull(o.oid1, '') = isnull(n.nid1, '')
			AND isnull(o.oid2, '') = isnull(n.nid2, '')
		) a
	WHERE (
			a.oid1 IS NULL
			AND a.oid2 IS NULL
			)
		AND isnull(a.oc, '') <> isnull(n.nc, '')
		OR a.oc < n.nc;

Table ‘old’. Scan count 1, logical reads 6, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.


Table ‘new’. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.


SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 148 ms.

Back to scenarios 7 – 9

One thought on “T-SQL Tuesday #114 Puzzle Party”

  1. Pingback: T-SQL Tuesday #114 Puzzle Party – Roundup | Matthew McGiffen Data

Leave a Reply

Your email address will not be published. Required fields are marked *