The ORDER BY used in filling the temp table has no effect on the column order in the XML result, because the DISTINCT keyword in your subquery (SELECT DISTINCT ColumnName...

I don't get it.  All the documentation links posted say it has to be 1 and 1 for memory-optimized tables.  The example posted above shows an error message stating it...

You will only get as many rows as the nodes at the level of your XPATH expression.  If your XPATH expression tells it to drill down to SupplierMapping nodes, then...

A lack of Referential Integrity (foreign keys, constraints, etc) will certainly affect execution plans, and not in a good way.  From their Wikipedia page: "Uniface applications are database- and platform-independent".  Meaning...

It occurred to me that instead of randomly reordering the Employees over and over again for each DataTable row, the NTILE function could be used to randomly divide DataTable into...

At first glance, you could do this with a correlated subquery.  This would pick a random X or Y employee for each DataTable row.

UPDATE dbo.DataTable SET...

You probably need to use a Derived Column transformation to define a varchar(10) column from the expression "LEFT(xyz, 10)".

Changing the metadata to say you only want 10 characters...

If you're going to run this in a multi-server window, you may not want separate results for each database.
Also, I deal with servers with different collations so at times...

The ? placeholder will be replaced by the database name, so just use it as a string literal.

EXEC sys.ms_foreachdb '
SELECT  ''?'' AS DBName, t.name as TableName,...

Creating a view requires DDL admin rights, which are usually a higher level than read or write.  I don't know if the combination of ddladmin and denydatawriter would even work.

If you are reading one row at a time in the CreateNewOutputRows method, and passing the data from each one to the appropriate ScriptBuffer.AddRow method, then it should not spool...

Why not simply delete batches until there are no more left?  I don't see why the temp tables and transaction are necessary, the procedure just exits if an error occurs.

WITH stockRatings AS (
  SELECT stockUID,
    rating,
    stockRatingCount = COUNT(*) OVER (PARTITION BY stockUID),
    stockRatingOrder = ROW_NUMBER() OVER (PARTITION BY stockUID ORDER BY rating ASC)

The sys.dm_exec_sessions view will show jobs running TSQL job steps with a program name like "SQLAgent - TSQL JobStep (Job 0xFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF : Step 1)".  You can parse out the job...