The point is not using exactly that table, it could be another table. The point was to demonstrate the logic in the original query, in a easier fashion.

DECLARE @StartYear SMALLINT = 2017;

SELECT  DATETIMEFROMPARTS(@StartYear + number, 1, 1, 0, 0, 0, 0) AS StartDate,
        @StartYear + number...

-- Local helper variables
DECLARE@StartOfWeek DATETIME = DATEADD(DAY, 7 * @Week + DATEDIFF(DAY, '18940108', DATEFROMPARTS(@Year, 1, 4)) / 7 * 7, '18940101'),
@EndOfWeek DATETIME = DATEADD(DAY, 7 * @Week...

DATEPART(ISO_WEEK, @thedate) ???

Assuming "dates" could only be a number between 1 and 31...

DECLARE @UnitMembership TABLE
        (
      ...

Ok then. Apply some relational division.

SELECT     t.Product
FROM       dbo.table1 AS t
INNER JOIN dbo.table2 AS x ON t.Product LIKE...

Put your search words in another table.

SELECT  t.*
FROM    dbo.table1 AS t
WHERE   EXISTS (SELECT * FROM dbo.table2 AS x WHERE t.Product LIKE...

It is worth mentioning that REORGANIZE does not update statistics. REBUILD does.

It is also worth mentioning that omitting FILLFACTOR defaults index fillfactor server setting (which is set to 0 when...

WITH cteParse(Id, Name, IdentificationString)
AS (
        SELECT  Id,
            ...

SELECT CONVERT(CHAR(8), DATEADD(SECOND, 60 * 526.30, '00:00:00'), 8)

Mine is still old-fashioned scalar function.

No wonder mine is faster. SQL Server managed to parallelize my function. With MAXDOP 1 my function runs in 1600 ms and Jeffreys in 1100 ms.

I am using an 8 core laptop with 8 GB of ram and 512 GB SSD disk, SQL Server 2019.

I get consistent 1100 ms with Jeffreys cte.

I get 450 milliseconds when using the algorithm as a scalar function.

I get 1000 milliseconds when using the algorithm as an inline table-valued function.

Using the same 1 000 000 rows...