How is "devicehistory" clustered?

If it's not clustered, or clustered by something irrelevant like an identity column, you could cluster it by recorddate and vastly speed up your queries.

Also, you should...

I expected that, just wanted to be fair about it 🙂 .

The optimizer still needs improvement.

Did you adjust the original query so you're doing an apples-to-apples comparison?

SELECT *

FROM #myData md

CROSS JOIN #patterns p

WHERE

md.descr LIKE p.pat

Is that really necessary?

Won't SQL treat computations on a declared variable as a constant, so they are still...

--edited out, essentially same code, lol.

You should have something in place that checks for disk hardware errors, with immediate notification and fast response.

And do proper monitoring of the suspect pages table and the SQL error...

178M rows shouldn't be a problem per se, if you index properly.

THE biggest factor is getting the correct clustered index.

Get that right, you should have no issues with that many...

I use identity as a clus key only as a last resort. Usually there's a better business key value that yields much better overall performance.

On a table I knew...

Why use the CHAR function to convert it back to a character?

Why not just check for the ASCII value?

I would do a query with:

SELECT

COUNT(*) AS [Total],

COUNT(DISTINCT column_1) AS [Column_1],

COUNT(DISTINCT column_2) AS [Column_2],

...

To be safe, I would move everything.

If/as you feel a particular object is not needed, rename it (or move to a different schema/db, if you prefer). See if any...

If you can, for now I'd just move the remaining tables temporarily to another filegroup, completely emptying the original fg, then shrink it. Finally move the remaining tables back...

Did you deletes of lots of table data in some cases rather than table drops?

At any rate, just to be sure, I would run sys.dm_db_index_physical_stats () on remaining tables to...

The setup and maintenance on full-text indexes is not trivial.

It's not a problem for a full-time DBA, but if you're not a DBA, it can be a tricky thing, especially...

I wouldn't automatically add a leading %. That forces a full scan which is often unnecessary if the requestor knows what the first name starts with.

Instead, allow the user...