My best guess is that the value is a pseudo-"Julian" date (as IBM called it), i.e., that the last 3 digits are the relative day number of the year.

Don't model the code or the logic, model the data.  Do logical modeling first, then convert that to a physical model.  Don't just start out with a physical...

Frankly, the attitude wouldn't bother me so much if what he was posting made sense in the real world.  But it just doesn't.  Making identifier values character(10) simply because you...

Why avoid 26 REPLACEs?  They perform pretty well; I guess unless maybe you have a huge number of rows?!

You might be able to do something simpler, depending on your specific data and requirements:

SELECT
  [Keys],
  REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
  REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
  REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
  REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
 ...

Btw, I also added a clause to the TAC join:

LEFT JOIN [dbo].[Contacts] TAC WITH (NOLOCK) ON SAC.Owner_Id IS NULL AND ...

You might gain some slight efficiency...

Look for articles or books by Itzik Ben-Gan.  The books are better overall for training, although they will cost you a few dollars.

 SELECT S.Id,
   T.Id,
   COALESCE(SAC.Contacts_Id, TAC.Contacts_Id) AS AssignedContacts_Id,
   COALESCE(SAC.OwnerType, SAC.OwnerType) AS OwnerType,
   COALESCE(SAC.ContactsId, TAC.ContactsId) AS ContactsId,
   COALESCE(SAC.Description, TAC.Description) AS Description
  FROM [dbo].[ChildTable1] S WITH...

You can use a CROSS APPLY to adjust the row to make it much easier to analyze values.  I can't give you more details than this right now because your...

You need to look at some actual stats, from the system views SQL provides, rather than just guessing.

The sys.dm_db_index_* and sys.dm_db_missing_index_* views can give you valuable stats on...

You don't want to store a string, use a numeric code instead; strings would be a huge waste of resources and are harder to change  Also, I'd separate the present/not...