1) Yes, doing that does violate normalization, including 1NF.  The text "discontinued effective 1/1/2011" has a date embedded in text.  That alone is a clear violation of 1NF.

2) It's...

For best overall performance, first review index stats to make sure you have the best clustering index on every table. (Hint: The majority of time, this is not an identity...

I believe that to index the column, it must be persisted.  If it's not persisted already, SQL will persist it when you add the index on it.

You could instead just change the "ON DELETE" option on the FK.  For example, to "tell" SQL to automatically set the referencing key to NULL (of course the referencing column(s)...

Again, I can imagine very few scenarios where a nonclustered index on a temp table would make real sense, typically that just wastes disk space.  Just cluster the table that...

Yeah, straight forward join, for a relatively small temp table, I'd probably go ahead and recluster it:

CREATE CLUSTERED INDEX CL_HIT_RANGE ON #HIT_RANGE
(

For the first part, use a clustered index not a nonclustered index.

Presumably you'd want to changce to cluster on RangeLow, RangeHigh for the part of that code that...

Which table specifically has 21M rows: tblDE_A10 or tblDE?

Do you use ArcServe backup?  [Or its equivalent, if it's now been bought out by another company.]  Somewhere in the mists of my mind I believe some backup package created...

That is my understanding as well.  If the job owner has sysadmin-level authority, then the job runs under the Agent's account.

I use view:
sys.dm_db_index_operational_stats
for that type of analysis / review on a given table/index.
Be aware, though, of the potential volatility of this data, based on the quote...

The "best method" depends on how you want to do it.  Assuming you want to do it via T-SQL, then maybe something like below (I've omitted any error/missing object checking):

DECLARE...

In short, yes, ignore "query cost relative to batch".  There's no question that the new code is vastly more efficient.

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...