Small tip: you can always do PRINT/SELECT instead of EXEC of  dynamically built query, then copy/paste it to SSMS and you'll see all syntax errors.

You'll always have single plan for single statement. Stored procedures can have several queries so there could be several plans. This is not the case with scalar functions.

Common practice is...

Steve, can the SQL Prompt colorize by user?

It's not exactly what you ask, but Redgate SQL Prompt allows you to create color for tabs. I usually put RED to all environments where I need to be more...

Not only OUTER APLLY is the problem, you can't use MAX function in the indexed view. Check "Additional Requirements"

https://docs.microsoft.com/en-us/sql/relational-databases/views/create-indexed-views?view=sql-server-ver15

I would not recommend using MERGE. First of all, how would you compare that there is diff? Join by all columns? Then what about nulls?

Moreover MERGE functionality is buggy... Please...

Could you please provide more details as it is a bit unclear what do you mean. Please provide some data examples, what you are getting wrong and what exactly you...

So, as far as I understood you need to insert records into Table2 from Table1 that do not exist in Table2. Then you can use following approach:

INSERT...

Refresh statistics

Just small tip, if you add sample data then it is more useful for people helping you if you provide it by DDL and data inserts. In that case the...

Please provide expected result

First of all, don't add SQL code as a picture, add it as a text. There is "{;} Insert/edit code sample" button to make the code highlighted properly.

According to your...

It's hard to optimize without having access to the database, but this is my try:

WITH exclusions
AS
(SELECT
[legalReference].[legalID] AS [Role_ID]
FROM [legalReference]
WHERE [legalReference].[legalType] = 1
AND [legalReference].[RoleID] = 20000
UNION ALL
SELECT
[Groups_Roles].[RoleID]
FROM [legalReference]
JOIN...

I'm sorry, but this is Microsft SQL Server forum, not MySQL. These are completely different products.

When you compare it can return either true or false. If it is true, then the row is returned otherwise - not. "ColumnName" returns some numeric value expression does as...