A COMMIT will not drop the temp table.  You need to drop it yourself.

A ROLLBACK will drop a temp table if it was created within the transaction, but...

Do you really want every match from Table_B to Table_A or only the most relevant match?  If you want to list every role, then this join will always be very...

Since you mentioned performance specifically, this might perform better:

Select Count(Distinct HB.AbillNo) AS AbillNo
FROM HB WITH (NOLOCK)
Where HB.st = @StatusCode And
    Exists(

Did you partition the columnstore clustered index on posted delivery date also?  That would make sense if it was best to cluster the row store on that column.  It's important...

"AVERAGE" isn't a SQL Server function, afaik.  It seems easy enough to get the result in SQL Server, but I'm not sure that would help you.

Do you want...

Maybe add a "NOT" flag to the conditions table.  If the flag is not on, you do a normal "LIKE" to join.  If it's on, you do a "NOT LIKE"...

Not 100% sure, but I believe it is:

create table ... (column1 ..., column2 ...,
primary key nonclustered ( column1, column2 ) ) with ( memory_optimized =...

You're likely going to need dynamic SQL to fully implement essentially free-form conditions, but to do what you've specified so far, you can do it with just standard LIKEs:

I believe session_ids can be re-used, so getting a session_id wouldn't necessarily tell you anything.

As noted, if you need to get that info, add a trigger to capture...

Yes, for testing purposes, adding an identity to guarantee uniqueness should work just fine.