Force SQL Engine to prepare Non-Null plan only for a query that use variable

Question

I have following two query approaches. The Approach 1 works in 20 seconds. The Approach 2 did not complete even after hours.

Approach 1

SELECT *,
        SUBSTRING(IDAndCode,CHARINDEX ('$', IDAndCode)+1, LEN(IDAndCode) - 2) AS ICDCode
From dbo.MyTable
WHERE F.Fact_Diagnosis_BK LIKE 'SKI-CE:'+'%' 
GO

Approach 2

DECLARE @DelimitingCharacter CHAR(1)
SET @DelimitingCharacter = '$'; 

SELECT *,
        SUBSTRING(IDAndCode,CHARINDEX (@DelimitingCharacter, IDAndCode)+1, LEN(IDAndCode) - 2) AS ICDCode
From dbo.MyTable
WHERE F.Fact_Diagnosis_BK LIKE 'SKI-CE:'+'%' 
GO

Note: The above two are simplified queries. The actual queries can be seen in the execution plan below.

Execution Plans

Actual Execution Plan – Approach 1 https://www.brentozar.com/pastetheplan/?id=rJlcWTk3m

Estimated Plan - Approach 2: https://www.brentozar.com/pastetheplan/?id=SJddO3en7

Note: The plan for Approach 2 uses Nested Loop join instead of Hash Match join

Question

I know that this can be fixed by adding OPTION (RECOMPILE). If I understand it correctly, the slowness is happening since SQL Server is creating a plan to accommodate NULL possibility of the variable.

In my case, the variable will be Non-Null before the query is executed (guaranteed)

1. In SQL Server 2016, what are the possibilities to tell the SQL engine that the variable will be non-null and prepare a plan only for such condition, without using OPTION (RECOMPILE)?

2. Is the slowness called parameter sniffing? (There is no parameter here. It is a local variable in a stand alone query)

Note: I am trying to see options to get the query running faster without using RECOMPILE.

References:

1. SQL Server Query: Fast with Literal but Slow with Variable
2. Improving query performance with OPTION (RECOMPILE), Constant Folding and avoiding Parameter Sniffing issues
3. Parameter Sniffing vs VARIABLES vs Recompile vs OPTIMIZE FOR UNKNOWN
4. Local Variables vs. Parameterized Stored Procedures in SQL Server

Answer 1

Following are the three ways that can fix the issue. More approaches are welcome.

Solution 1

Instead of a variable(@DelimitingCharacter), use a temporary table.

CREATE TABLE #VariableHoldingTable (DelimitingCharacter CHAR(1) NOT NULL)
INSERT INTO #VariableHoldingTable
SELECT '<'

--And do a join

  INNER JOIN #VariableHoldingTable V
    ON 1 = 1

Execution Plan: https://www.brentozar.com/pastetheplan/?id=rJpehe5hQ

Solution 2

CREATE CLUSTERED INDEX CX_DXID on #MultipleDiagnosisDXs(DXID)

Execution Plan: https://www.brentozar.com/pastetheplan/?id=B1nKne9nX

Solution 3

OPTION (RECOMPILE)

Execution Plan: https://www.brentozar.com/pastetheplan/?id=BkRgalqnX

Answer 2

CREATE TABLE #MultipleDiagnosisDXs (DXID INT Not null)

Notice not null here.After populating create index.

Create Clustered index IX_DXID on #MultipleDiagnosisDXs(DXID)

In query correct,

WHERE EXISTS (SELECT DXID FROM #MultipleDiagnosisDXs A where A.DXID=B.DXID)

No need of order by clause

Above change is must.

You should once try EXEC sys.sp_executesql .

You should also try Row_Number once,with this you do not need second Select statement and no need of temp table.

Do not use "*".

Just try With (Nolock) hint: If it help then we discuss more about it.It is not always considered to be best practice, but try once.