The worst schema I had to implement: A customer wanted us to reboot the server for patches on the first friday of the month around 1am.

How do you...

(Dup post, removed)

;WITH cte_data AS (
    SELECT CAST(50.10 AS numeric(5, 2)) AS HRS
    UNION ALL
    SELECT 50.77
)
SELECT HRS
FROM cte_data
WHERE HRS...

LineCount must exist in your table, not the system table; if so, then change the alias before the column name:

...

INSERT INTO dbo.PrimaryHist

SELECT 'UPDATE'

,s.login_name

,i.batch_id

,i.LineCount --<<--

FROM INSERTED AS i

CROSS JOIN sys.dm_exec_sessions AS...

Page compression is generally very effective at reducing data size.  But you will need to either: (1) shrink the data file after compressing, which will take a long time and...

That's the equivalent of a CROSS JOIN:

SELECT 'UPDATE'
      ,s.login_name
      ,i.batch_id
  FROM INSERTED AS i
  CROSS...

Or, just in case there might be: '64.0 MB,restricted growth to 2097152.0 MB'

SELECT * FROM #tbl_datafile_list WHERE Autogrow LIKE '%[^A-Za-z]restricted[^A-Za-z]%'

Triggers don't necessarily suck at performance, it depends on how they are written.  Neither does CDC perform all that poorly; in fact, I think it's extremely unlikely you could write...

;WITH cte AS (
    SELECT @@SERVERNAME AS ServerName,
           ISNULL(b.database_name, d.name) AS DatabaseName,
  ...

Or make the value column a sql_variant data type.

SQL may not evaluate things in the order you expect it to, so something like that could indeed be happening in certain circumstances.

;WITH cte AS (
    SELECT @@SERVERNAME AS ServerName,
           b.database_name AS DatabaseName,
   ...

Still, be sure you do not have some type of accidental join / cross join that will generate extra rows.

I'm not sure I understand correctly, nor what result you expect to see from the sample data, but maybe this(?), based on my reading of your requirements so far:

            
November 1, 2023 at 7:11 pm
            
#4319191

            
            
                                                
        

Sadly, without giving IMPERSONATE to the users, I cannot let...