1) No, afaik.
2) Yes, [H]HHHMMSS.
3) I've no longer got CDC jobs really, but I don't remember seeing that phenomenon when I did, very interesting.

WHERE/ON SUBSTRING(string, CHARINDEX('-', string) + 1, 5) = ...

To get the avg total time, you could do this:

DATEADD(SECOND, SUM(DATEDIFF(SECOND, 0, TotalTime)) / COUNT(TotalTime), 0)

Here's a rewrite of the first query (before the UNION ALL).  I don't have time to do both but they look very similar.  The second two subqueries can be combined...

Create a new table on database A to hold database B's data: host name, OS info, etc.  For the sake of argument, let's say the new table is named "ip_details". ...

Are you licensed for all the CPUs?  At one time, the affinity mask was used to run on machine that had more processors than you were licensed for, by cancelling...

Not easily, no.  With the current query,for example, we have no way of knowing which row is the last pax.

Btw, you should truncate not round, or you will...

This is relatively straightforward (the difference between start time and end time, for selected hours only), but you'll need to post directly usable data.  That is, CREATE TABLE and INSERT...

The "standard" way to do that is to make sure the key doesn't already exist in the table to be insert to.  If you need to, delete dups from the...

The table's already clustered by [TRANSACTION_TIME].  That's fantastic, it'll make your job so much easier.  We'll just want to increase the fill factor from 90 to 99 for the historical...

Need to see the definitions of indexes too, especially for the clustered index (or pk, as that will be the clustering key unless you explicitly say it isn't).

You...

Is(are) there any index(es) at all on this table?  If not, you'll definitely want to "create a brand new table to insert into while the old table data is being...

What you've stated and shown is contradictory.  Is transaction_time a "string", as you stated, or a datetime, as your code shows?

Assuming it's a datetime, you don't really need...