If there's no index at all, might as well make it a clus index on column1.  Yes, an index should help, since it will avoid scanning the whole table for...

CASE, ISNULL and COALESCE are all an integral part of SQL Server and follow its general syntax.  It's because IIF is so unlike other other functions and expressions in SQL...

Does the table have an index with column1 in it, or preferably where column1 is the first column in an index?  If no, SQL would have to scan the table...

If the main table is really that narrow (few bytes), then compress the non-clus index to reduce the pages more:

CREATE UNIQUE NONCLUSTERED INDEX IX1_IattribINT ON dbo.AttribINT (...

I'd suggest first try creating a non-clustered index on ( OID, AID ).  Cluster the temp table on ( OID /*and AID if available*/ ).

CREATE UNIQUE NONCLUSTERED...

Need to see the DDL for AttribINT, including all index definitions.

I dislike IIF ... its "familiar" for people that use similar functions in Excel, but I think CASE is better in SQL

IIF only has two outcomes, true/false, and to...

Yeah, that code should do it.

But if you're using the column name "ClientNotes" directly in a SELECT you will get the original column value, not the one after the REPLACEs. ...

Most typically:

select name,count(*) as name_count
from dbo.table_name
where name like '%ra[jm]%' and P = 1

IIF([var1Title] != '', [var1Title] + ISNULL(' - ' + [var1Topic], '') + ISNULL(' - ' + [var1Name], ''), '') AS Item1

I'd avoid a guid if at all possible.

Make sure you have an index keyed first on c1, so that determining the max(c1) is a single, quick seek.  An INSERT of...

SELECT 
    phone_string,
    phone,
    --LEN(phone) AS phone_len,
    CASE WHEN 
    CASE WHEN...

I wouldn't make the Status a key column, since it's likely to change so often, at least in the earlier life stage of the row.  The rows are small anyway,...

Ordinarily, I'd agree but to consider the span of...

But does the Adjacency model actual satisfy BOM requirements?