I'd say just use > CHAR(0) if you're going to go that route.  Btw, wouldn't you have to use >= CHAR(9), just in case only a tab char was there?

And that's why I wrote the code the way I did.  The other way is somewhat easier to code, but potentially far worse in performance.

The underlying rule is:

Never use a...

2) these display strings sort correctly in temporal order. They are not ambiguous.

As I stated, In the real world dates are sometimes given as YYYY-DD-MM for certain regions of the...

Actually, the format YYYYMMDD is perfectly acceptable under ISO.  I don't know why Celko insists on repeating his false claim over and over about YYYY-MM-DD despite being told that it's...

You probably need something like this:

WHERE (AKey LIKE 'AAA%' OR AKey LIKE 'BBB%' OR AKey LIKE 'CCC%' OR AKey LIKE 'DDD%' OR
    AKey LIKE...

Don't convert a column to a different data type unless you absolutely can't avoid it (sargability and all).  For your situation, this should handle both dates and the -1 properly. ...

Thanks for the follow up.

Now it's back to the old approach -- you have to go thru the query plan query by query and tune each separately (and perhaps consolidate...

There's nothing per se wrong with CREATEing the table using INTO; indeed, it can help prevent future errors when data types changes, for example.  But, you don't want to load...

Hmm, interesting.  With those symptoms, I'd probably first look for a physical reason.  Bad RAM, bad NIC, bad local drive, some physical failing on one node vs the other.

An inline TVF should perform better, although how much better it's very difficult to estimate.  I've done the best I can converting this in a short time, you may need...

Another version, trying to do the least prep work possible (although for determining day or month first, the entire staging table may need to be scanned):

--Assumes that...

ALTER TABLE dbo.table_name ADD MinuteToHour AS CONVERT(char(5), DATEADD(MINUTE, CONVERT(int,[sun_total]), '19000101'), 108);

We're seeing only a partial plan with none of the actual stats and no sql query text.  I can't offer any real guidance with such limited info.

I don't think going back just 1 row will be accurate, at least as I understand the requirements (which, of course, could be incorrect).  For example, assume times of:

12:24; 12:25;...

Something along these lines should let you generate the code:

DECLARE @sql nvarchar(max)

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