Just went through all that code and lordy.

First, they're using a MAX datatype on address columns.  I guess they never want to do lookups on those columns. 😀  Either that...

Lordy... I wonder if the partitions are "hourly".

Heh  for a lot of folks, they actually did wait until it broke. 😀  Most every one else (that I knew at the time) waited until 1999.  I made my...

It also increases the number of characters to be transmitted by a whopping 25% compared with "YYYYMMDD" and it was specified and designed in an age where 110-300...

Lordy...  looking at this again, I see partition numbers that exceed 5500 partitions.  If you divide 5500 by 365, that's 15 YEARs of days if you've partitioned by day.  I'm...

Spot on, Grant.  Another thing that the use of variables affects is actually whether or not even properly setup "Minimal Logging" will occur.  If you're trying to do Minimally Logged...

To keep the record straight, the ANSI/ISO SQL standards allow only the ISO – 8601 "YYYY-MM-DD" format for dates. The. The reasons we pick this one were:

1) it's not...

<headdesk> 😀 Fixed the post.

Look for queries which are heavy on BLOB processing.

You mentioned BizTalk - do you have some massive FOR XML queries? It may be either massive XML (JSON, you name...

Ah... almost forgot.  One of the things that MS did very, VERY wrong is how they interpret the (secondary ANSI) format of YYYY-MM-DD when used for direct conversions from strings...

Ugly, but this works:

DECLARE @SchedStart VARCHAR(25) = '2020-06-07-12.30.00.00000';

SELECT @SchedStart
      ,CAST(STUFF(STUFF(STUFF(LEFT(@SchedStart, 23), 11, 1, ' '), 14, 1, ':'), 17, 1, ':')...

Just a different take on the same subject...

If the dates and times always follow the same format, which would also be required when using STUFF, then the hidden ANSI capabilities...

Thanks for the correction, Scott.  I plumb forgot about that aspect of CHECKs (mostly because I make end-date columns NOT NULL).  They're like FK's in that aspect... they don't have...

No sir... that's absolutely NOT correct.  In fact, they can go for months without any page splits (not even supposed...

Sorry... had a "Scott" on my mind from a different post.  Corrected my previous post.