I would recommend that you work with the business to identify the columns that make a unique row. They have to exist, otherwise how would you differentiate between two rows?...

Glad you got it working.

This part of the query is still going to cause problems: WHERE CompleteTaxonText Like '%rupicapra%'

That kind of wild card search can't make use of...

Congrats, Jeff. Quite an achievement. I used to try and keep ahead, but haven't bothered.

Now I wonder where I get to 1mm first. Here or in miles flown. At...

Glad you got it working.

This part of the query is still going to cause problems: WHERE CompleteTaxonText Like '%rupicapra%'

That kind of wild card search can't make use of statistics and...

Let's be really clear, SSD are indeed, insanely fast.

If you put everything onto one SSD can you overwhelm it, despite it being insanely fast?

Yes.

There's a reason organizations like Pure Storage...

Extremely important thing to remember:

A common table expression (CTE) is not a table. It is an expression. A query. It does not, in any way, represent data storage, temporary or...

Well, defining stuff like this as "normal" is probably not the best way to describe it. However, is something like this expected? Yeah.

One of the hardest things is recovery from...

I'm not going to try to unpack all of that.

The optimizer thinks that the filter on  C_MT_B_COMM_TO_PTY.PARTY_TYP_CD + ':' = 'Contact' + ':' is going to do a lot more...

I don't believe the load balancing aspect, at all.

Yes, you can move some queries offline. However, that is not load balancing. Load balancing is the automated process of distributing where...

Or get the older version. Forgot to mention that.

You sure you got the question right? Looking through that list I can only see one thing that IS a benefit. Replication does not load balance. Replication doesn't distribute queries....

I am not a fan of debugging within T-SQL queries, however, if you want to do it, SQL Server Management Studio is free and it does include debugging.

The...

It's the code. I know you've said you can't affect that, but that's where the problem is. You have to use the correct data types. That's how you fix these...

I checked this table it has no FK, or triggers, and character fields are defined as Nvarchar.   There are NO indexes or a PK set of this table.

Are you...

Yeah, absolutely.

Although, I have good news. There is an open source software that does a lot of this, OpenQueryStore. I saw this when it was an in-house private bit...