there's one exception - your log file grows stupidly big and...

I don't think it'd a good idea to have more than one log file for a database:

https://www.sqlskills.com/blogs/paul/multiple-log-files-and-why-theyre-bad/

Try doing

select TOP (50000) * from EXT_data_Hist_TbL

on each database, without anything in the WHERE or ORDER BY, and see how many reads it does.

I think this is a direct conversion of your SQL, just subtracting 2 inline SQL statements:

SELECT (SELECT count(p.[peopleId])
         ...

Grab a copy of NGrams8k and you can do this:

Declare @Temp Table(SomeId INT IDENTITY, [Data] VarChar(8000))

Insert @Temp ([Data])
Values('hello my name is john 07999999999 smith...

Grab a copy of NGrams8k and you can do this:

Declare @Temp Table(SomeId INT IDENTITY, [Data] VarChar(8000))

Insert @Temp ([Data])
Values('hello my name is john 07999999999 smith 07888888888 this...

Once you have the numbers in separate rows you can make...

I'm assuming that rows are added roughly in CREATE_DATE order. That means you will find the earliest rows on db1 very quickly as no rows have been deleted.

On db2 you...

A recursive CTE will allow you to select more than one number from a line

DROP TABLE #t1
go

CREATE TABLE #t1
(
    SomeText VARCHAR(500)
);

INSERT #t1
(
  ...

• This reply was modified 6 years, 9 months ago by Jonathan AC Roberts. Reason: Changed PATINDEX('%[^0-9]07[^0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][^0-9]%', ' ' + t.SomeText+' ')

How long does it take to do the select on just a small not null column with the same query on both databases? e.g.:

select TOP (50000) CREATE_DATE
from...

I didn't mess with any indexes on the table you provided.

Here are a few methods:

Update Windowed SUM/COUNT using ROWS BETWEEN

/*-- ****************************************************************************************
-- Update Windowed SUM/COUNT using ROWS BETWEEN
--...

I do have a way to avoid that problem that will blow the doors off of most other methods for calculating running totals but it does take...

I do have a way to avoid that problem that will blow the doors off of most other methods for calculating running totals but it does take a bit...

This is a classic Catch-All query.

<li style="list-style-type: none;">

• If you check your execution plan, you will note that you have all table/index scans and no seeks.  That is...

I'm not sure what the parameter values mean, but I think you might have meant to check if the parameter is null not the column?

SELECT *
 ...