SQL Server 2016 does allow far more minimally-logged INSERTs to an existing clustered index than any earlier version of SQL.

And, in some instances at least, it does allow simultaneous INSERTs...

I'm not sure.  I don't know if SQL can do parallel writes to a new clustered index.

Given all the possible table options -- data compression, large value types out of...

I think it could.

I'd certainly expect the initial SELECTs from the table to be done in parallel, and thus likely the sorting as well.  But the writes to the final...

Try using datetime as the destination data type, rather than smalldatetime.

Instead of using WITH (NOLOCK) on every system table, use SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED for the session.  That way you don't have to worry about missing one...

I tried to come up with something other than Extended Events because of this in OP's q:

Extended events is an option, but it's doubtful that my company will approve this...

I'd avoid INFORMATION_SCHEMA views completely.  I've found them to be slow (I understand that the view definition doesn't look like it should be slow, but real-world use says otherwise; if...

You can use "WITH (NOLOCK)" on system views, and I suggest you do so here.

Your join on the types table is incorrect, you should use user_type_id not system_type_id.

If you're worried...

It's not really an instance-level trigger, it's a db level trigger for the master db, because all logins are created in the master db.  The DDL event would be CREATE_LOGIN...

To the point that others have made, ORMs will frequently make the mistake of using some form of unicode datatype and, if the underlying datatype doesn't match, then no...

You need an index on the ProximityCardNumber column in that table, or SQL will have to scan the full table every time the query runs.

SELECT Column1, COUNT(*) * Column1 AS SumColumn1
FROM dbo.table_name
WHERE Column2 = '1'
GROUP BY Column1
ORDER BY Column1

Reserved word, just use a different name.  I...

This looks like a recursive update, so it's best to go row by row (or, if you are willing, use Moden's "special" update method using a specific clustered index structure).

But,...

Corrected the code a bit, and added any leftover amount to the first work day of the month.

;WITH base_data AS (
    SELECT
  ...