DELETE in batches rather than all at once, as long as each DELETE won't require (up to) a full scan of the table to find the rows to delete.

If you...

Compress the data.  Look at both page compression and COMPRESS/DECOMPRESS for large char columns.

You might also look into data compression (both page compression and COMPRESS()/DECOMPRESS() for long text data).

Agreed.  Partition the data if you need to update stats on a very large table and you can separate active data from inactive data by partitioning.

NOLOCK makes sense in very limited cases.  For example, for lookup tables, such as a table of state codes.  There's zero reason to do locking when reading that table.

The big...

If you want every day in every month, then something like this:

;WITH
cte_tally10 AS (
    SELECT * FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS numbers(number)
),
cte_tally100 AS (
  ...

Maybe this, if I understand the requirements correctly:

SELECT *
FROM POSDLYCHMQ P
LEFT OUTER JOIN (SELECT P0ISBN, p0CHN, P0WEDT, 
COUNT(DISTINCT CASE WHEN cast(cast(sq.P0ADAT as varchar(30)) as date) 
BETWEEN DATEADD(DAY,-7,DATEDIFF(DAY,0,cast(cast(sq.P0WEDT...

As Jeffrey stated.

Most importantly, make sure you have the best clustering index on all the main tables so that you limit the scan/search activity on the tables.  (Hint: most often...

You don't need to actually count VLFs yourself, you can use sys.dm_db_log_stats instead (via column total_vlf_count).

You could fairly easily capture previous count(s) and compare them to current count using a...

The problem in  your original statement is that your calc -- CHARINDEX('^',RIGHT(UNDERLYING_SYMBOL, LEN(UNDERLYING_SYMBOL) - CHARINDEX('^', UNDERLYING_SYMBOL))) -- involves only numeric values, so the result is numeric.  Since it's being assigned...

update   dbo.MainTable
set     UNDERLYING_SYMBOL = STUFF(UNDERLYING_SYMBOL, 1, 1, '')
where    underlying_symbol LIKE '^%'

It's possible it would be far better to cluster the dbo.User_Events table by AuditTimestamp rather than UserID.  That could be a significant change, however, so if you would first run...

And to make the code not reliant on a particular DATEFIRST setting, don't use DATEPART, instead do this:

...
AND DATEDIFF(DAY, 0, a.date) % 7 <> 4 -- Ensure...

I need just the passenger events (0 or o,1).  But I think I may have gotten the answer from a colleague:

SELECT costCenterCode, scheduleDate, tripid, routeId, routename, activityId, eventOrder, CASE...

It's just another expression per value, not statement.  You could pre-code 10 or even 20 values without a lot of trouble:

SELECT ResourceType, CustomerID, DOB,
   ...