A standard "AFTER" trigger will work.  Keep in mind that the trigger does not have to be "all or nothing": it can let good INSERTs/UPDATEs apply and only reverse the...

Whether you use a single Tag table or not, a trigger would still be much easier than a constraint.  Why do you not want to even consider a trigger?

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: