What the heck... I'm just going to post what I have... See what you think of this.

USE tempdb;
GO
--====================================================================================================================
-- Start by creating 1M rows of test data in...

• This reply was modified 5 years ago by Jason A. Long.
• This reply was modified 5 years ago by Jason A. Long.

Quick question for @sharon-472085... How do you want to handle rows that have duplicate date-time values?

For example you have two rows that both have a value of '2020-08-26 00:00:40.000'? If...

Shifting gears back to the original topic, if the Recursive CTE method proves to be a bit slow, post back... I have a bit of an unorthodox but very...

Steve Collins- I didn't see a "function-ized" version of your code in the thread. If I missed it, point it out and I'll include it in another round...

2020 is definitely NOT...

• This reply was modified 5 years ago by Jason A. Long.

First - I 100% agree with everything Jeff said above...

Second - All your problems go away if you were to use Windows Active Directory security groups. Rather than assigning permissions...

Jeff - I'm looking and comparing my second function to your code to see if I can spot the reason(s) for the difference in execution plans and, in particular, the...

Like Ed said, you're closer than you think...

Rather than continuing to torture with trivia... the answer is...

"My ass is...

I've finally had a chance to look at all that more carefully and, man, that has a VERY interesting execution plan.  With a pre-split table, I might be able...

Steve Collins- I didn't see a "function-ized" version of your code in the thread. If I missed it, point it out and I'll include it in another round of testing.

• This reply was modified 5 years ago by Jason A. Long.
• This reply was modified 5 years ago by Jason A. Long.

Using my standard test harness... (query options: Discard results after execution and use DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS; between executions)

SET NOCOUNT ON;
GO
--  DBCC FREEPROCCACHE WITH NO_INFOMSGS;
 ...

• This reply was modified 5 years ago by Jason A. Long.
• This reply was modified 5 years ago by Jason A. Long.

Just to get an idea of what patterns exist in the TestData table...

SELECT 
f.char_pos,
COUNT(1)
FROM
dbo.TestData td
CROSS APPLY dbo.find_all_match_positions_JL(td.word_array_6, td.word_array_5, 2) f
GROUP BY 
f.char_pos
ORDER BY
COUNT(1) DESC;

Results...

/*
char_posoccurance_count
2,3119002
2,4115004
3,4112494
1,3112334
3,5112289
1,4111446
1,5111083
1,2111015
2,5110999
4,5106304
1,2,45374
2,3,54660
1,2,54635
1,2,34589
1,4,54449
2,4,54443
2,3,44441
3,4,54151
1,3,44124
1,3,53140
1,3,4,5329
1,2,4,5256
1,2,3,4232
2,3,4,5213
1,2,3,5119
*/

Here are my previous scripts converted to iTVFs...

USE tempdb;
GO
SET QUOTED_IDENTIFIER ON;
GO
SET ANSI_NULLS ON;
GO

CREATE FUNCTION dbo.find_all_match_positions_JL
/* ===================================================================
08/18/2020 JL, Created: will identify all of the position/character matches and tell...

My 15 yo brain is saying "ass/butt being divided infinity"...

I did a little work coming up this good test data. Here is what I cam up with...

It'll generate 100K (adjustable) rows with both 5 char and 6 char arrays...

• This reply was modified 5 years ago by Jason A. Long.
• This reply was modified 5 years ago by Jason A. Long.