Would you provide the actual T-SQL statement you were attempting to execute?

The fix is:

Select STUFF((Select Distinct + ', '+ CONVERT(varchar(20), A1.date, 105)

...

Since A1.date is a datetime, which has a higher precedence than varchar, SQL is attempting to add ',' to the...

Frankly the percentages from an estimated query plan are often so inaccurate they should just be ignored.

Look at the actual operations in the query plan, not the percentages.

Restore old msdb backup(s) to a different db name.  We typically add '_' and the date of the backup to the name.  Something like this:

RESTORE DATABASE msdb_20190915 FROM DISK =...

As to the actual code, a more straightforward way is to CAST to date, which will perforce remove the time value, then CAST back to datetime (this probably isn't strictly...

Getting rid of NOLOCK will only hurt performance, never help it.  That's not an endorsement of nor objection to NOLOCK in these specific statements, just a general statement of fact. ...

Quote: "We couldn't do much about the database design as it is a vendor specific database."

Understood.  Lol, the only thing potentially worse than a "programmer (non)'designed' table" is a "vendor...

Try below.  Sorry, I don't have time right now to explain the code.

SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
GO

CREATE TRIGGER [dbo].[Dupl_Rec]
ON [dbo].[Table A]
AFTER INSERT 
AS
SET NOCOUNT ON;

INSERT INTO dbo.Audit...

According to your table structure, there is no index on [MULE_BATCH_ID].  This means that SQL has to do a table scan to find the record.

You are doing a...

That is normal behavior for a FULL JOIN.  You need to adjust the final SELECT to reflect the fact that any table's results could be NULL, like so:

            
September 19, 2020 at 5:19 pm
            
#3789655

            
            
                                                
        

You didn't provide nearly enough info to analyze your index needs.

However, keep in mind that SQL strongly favors equal (=) comparisons over other types.  So, if your conditions are "WHERE...

Uh, no, they are designed exclusively to help performance.  That is, increased...

Btw, you're wasting bytes making that column nvarchar rather than varchar, since the only values possible don't include any non-standard chars.

Much better would have been to create an SSMS server group(s) with all the relevant servers in it(them), then fire off the script for a given server group(s)...

DDL triggers are a great option.  I use them in nearly every db to prevent actions we don't want taken.  As examples, in some dbs we don't want objects created...