For example:

ALTER TABLE #ACME ADD 
    new_column1 uniqueidentifier NOT NULL DEFAULT NEWID(),
    new_column2 int IDENTITY(1, 1) NOT NULL;

SELECT ca1.*
FROM #ACME A
CROSS...

You don't really need a pk or any other column from the main table.  Also, I assumed you're rather see blank than NULL for missing levels, but if...

Hmm, that doesn't seem right.  You must have added other columns to...

Although the PK is frequently also the clustered index, it's not necessarily so.  The keys of non-clustered indexes include the keys of the clustered index, which isn't necessarily the...

I manage hundreds of prod dbs (and hundreds of qa ones) and I have roughly a dozen that have RCSI.  The overhead is just so high.  [And if you want...

You don't really need a pk or any other column from the main table.  Also, I assumed you'd rather see blank than NULL for missing levels, but if not, just...

First, you should encode the string values to a numeric equivalent.  Imagine what must happen now when the name of a level changes.  For example, say that "Outdoor Accessories" had...

For just that specific column, you need to rebuild one of the existing indexes:

CREATE NONCLUSTERED INDEX [IX_game_boxscore_players_personID] 
    ON [mlb_json].[game_boxscore_players] ( [boxscore.players.person.id] ) 
...

Need to see the DDL including the index definitions for all tables in the query.

I still have to wonder on the appropriate use of NOLOCK. Even if the data does not change in a table, is NOLOCK...

I still have to wonder on the appropriate use of NOLOCK. Even if the data does not change in a table, is NOLOCK that much better...

For these specific queries alone -- since we know nothing else about this table -- you should change the clustering key on the table shown.

CREATE TABLE dbo.CustTravelReqLocPlan (

...

CONSTRAINT [PK__CustTrav__401DC63546C0E5F6] PRIMARY...

It seems there must be some error occurring when the script it exec'd.  Add some msgs to (1) verify if this is true and (2) if so, show the return...

(1) CROSS JOIN is not necessarily bad for performance.  If the CJ'd table is single row, it's definitely fine.

(2) The %s are OK here, since there's no easier way to...

Yes, both can make a big difference in OLTP as well.  The key is that it reduces I/O because fewer pages must be written to disk.