If you want to make it even faster you can add an index to the temporary table:

IF OBJECT_ID('tempdb..#pre_filtering') IS NOT NULL
    DROP TABLE #pre_filtering;

;WITH...

Try this instead, it splits it into 2 separate statements:

IF OBJECT_ID('tempdb..#pre_filtering') IS NOT NULL
    DROP TABLE #pre_filtering;

;WITH pre_filtering AS 
(
    SELECT...

Try this:

;WITH pre_filtering AS 
(
    SELECT DISTINCT 
           a.[DC], 
    ...

You asked the question in the SQL 2019 forum.

Try the code in the last comment I made.

You need a GROUP BY:

SELECT DC, UPC, stuff((select ', ' + MASTERCHAINNAME 
              from...

• This reply was modified 2 years, 6 months ago by Jonathan AC Roberts. Reason: Added where b.DC = a.DC and b.UPC = a.UPC

It's the version of SQL Server...

Create some test data in a consumable format (you should have done this):

DROP TABLE IF EXISTS #pre_filtering;

CREATE TABLE #pre_filtering (
    DC VARCHAR(50),
  ...

You might have to rewrite the query to...

[

Hi Jonathan,

Thanks for the inputs. I would think the last non-clustered idx is better to create. Also, I have a question on this, do we need to mention ROWID_OBJECT...

Your query can be simplified to this:

SELECT TOP(11) 
       c.rowid_object,
       c.creator,
    ...

Jonathan great. Thanks. I appreciate your help.

One more small question. This code basically compares items between ITEM/LOC and temp and returns if there is a mutual match between temp...

Jonathan AC Roberts I think yours work. Thank you.

Can I ask do you know guys what am I doing wrong?

I am trying to detect if items from the table...

Not sure what you are trying to do but you could put the results of that query into a temp table like this:

DROP TABLE IF EXISTS #temp;
CREATE...

And, in the process of making the primary key, it made yet another mistake (at least according to my personal standards).  If it's a #TempTable, then never name the...

Thanks for taking the time to do these experiments, Jonathan.

The reason it got the Zip right is because it's in quotes.  You also had to realize its mistakes on...