This topic comes up so much, I think MS should "fudge" a little on the relational model and create a type of "char array".  Then include not just selection against...

A long shot, but SSMS seems to use msdb for a lot of its actions.  I've seen bloated msdbs slow down SSMS considerably, even just expanding the db list.  So...

INSERT @TestTable2
(
    NameRecord,
    SingleName
)
SELECT ca1.*
FROM @TestTable
CROSS APPLY (
    VALUES(ID, NameRecord, Name1), (ID, NameRecord, Name2), (ID, NameRecord,...

Do be sure to update your statistics also, though, esp. when restoring SQL to a different version.

It's more common to use SUM(... THEN 1 ELSE 0 END), rather than COUNT, (and clearer, at least to me), so I've done that below.

SELECT

You have CREATE USER in the code twice (once IF'd and once not).

IF SUSER_ID('[Domain\IT Developers]') IS NULL
  CREATE LOGIN [Domain\IT Developers] FROM WINDOWS WITH...

This is fairly typical of the results I see for a child table.  The clus index is ( parent_identity, child_identity ) and the non-clus index is ( child_identity ).