Does is really matter if the numbers are sequential or is everyone just used to that?

If it doesn't really matter, you might just use an identity column and...

I prefer this alternative.  Not for performance reasons, mostly that it allows easier customization of the suffix chars to be assigned.  Sometimes certain chars, such as I and O, are...

Oops, correction:

UPDATE [EC_StoreAttributes]

  SET AttributeValue = CONVERT(varchar(2), GETDATE(), 12) -- 112)112) + /* 12 rather than 112 */
   SUBSTRING(AttributeValue, 3, LEN(AttributeValue)...

Based on your last comments, this looks like the only way that might really help.  Your keys columns are exceptionally non-selective, which makes it more difficult to get best performance.

UPDATE t
SET response_sol_id = x.[solicitation_id]
FROM #temp_stp t
 CROSS apply (SELECT P.solicitation_id
   FROM [SOLICIT].[gtm_doc_prod] P
   INNER JOIN [SOLICIT].[gtm_doc]...

Again, you need to cluster the table on those columns then.  You can adjust nonclus indexes all day and it most often won't do you much good.

Yes, there is.  It's called "SNAPSHOT ISOLATION".  That's almost exactly like Oracle: point-in-time for SELECTs, version rows stored to handle long-running queries, readers don't block writers, writers don't block readers,...

Hard to tell without selectivity / row count info, as Chris noted.

But, if the partition and dataareaid are how you typically query this table, and guessing that PARTITION...

UPDATE [EC_StoreAttributes]

    SET AttributeValue = CONVERT(varchar(2), GETDATE(), 112) +
        SUBSTRING(AttributeValue, 3, LEN(AttributeValue) - 4) +
       ...

Start fresh with a new db design!

Do a true logical design -- without NO carryover from current design and NO consideration of physical implementation (Oracle, SQL, etc.).  NONE. ...

Short answer:
You want a dynamically-generated, static trigger.  That sounds contradictory but it's not.

More detailed answer:
You must meet two critical conditions here:
1) the trigger must execute as...

Select Count(*) as XmlEventsCount,
    Max(case when x.Status = 'NOTE' THEN 'Yes' ELSE 'No' END) as SentToProvider
From TicketEvents x
Where x.RowID=1136394
Group By x.RowID

Nope, it actually doesn't.  By default SQL will make it so, but it doesn't have to be.