Here are queries to demonstrate the points above.  Assume for this example that B.col2 is a NOT NULL column.

;WITH cte_A AS ( SELECT * FROM (VALUES(1, 'A',...

...OUTER JOIN, if the filter is on the OUTER table, it needs to go to the JOIN criteria or it converts the OUTER JOIN to an INNER JOIN which...

• This reply was modified 6 years ago by ScottPletcher. Reason: Reworded, for clarity

You need to specify an explicit format (conversion code) for the date conversions, viz:

CONVERT(varchar(100),ISNULL(despatch_group_start_date,'01/01/1900'), <format_code_needed_here>)

OK, so I guess the AFTER trigger would have to use the deleted table to check for UPDATEs vs. just relying on the UPDATE() function.

CREATE TRIGGER dbo.TriggerSource_AFTER_UPDATE
...

I don't know, maybe I'm not thinking about it right, but I was anticipating something like below.  SQL checks a column value being UPDATEd and doesn't actually change it if...

I've had to use "extension" tables here a few times too, for various reasons.

If you have an identity column present, do remember / be aware of the issues...

I've had to use "extension" tables here a few times too, for various reasons.

If you have an identity column present, do remember / be aware of the issues with SCOPE_IDENTITY()...

For (1), you could add a column to the table itself to store the original login name there too (or, far better to save space, an id (that you assign)...

1.  No way around it.  The temporal table must exactly match the original schema.  My guess is that this provides efficiency in the way SQL Server internally implements the Temporal...

2. ...   Current trigger technology won't even directly allow the saving of LOB columns because the LOB values don't show up in the INSERTED/DELETED logical tables in standard DML...

Great article, very interesting and thought provoking.

Thanks again for taking the time to post this.

To give the best chance of using minimal logging, be sure to lock the table that is being INSERTed into:

...

INSERT INTO dbo.B WITH (TABLOCK)

...

That's an extraordinarily complex task when you really look into the more difficult cases, with honorifics (Dr., etc.), suffixes (III, IV) and so on.

I would do this process one time,...

Or this:

SELECT lastName, LEFT(lastName, 5) AS lName FROM myTable

SELECT SUM(Expenses) AS Expense, Vendor, MAX(UploadDate) AS UploadDate
FROM dbo.a_table
GROUP BY Vendor