Yes... the deprecated types should be changed to the modern types... or at least according to MS.  So here's how to fix some of the mistakes MS made when creating...

First, nothing has been attached that I can see.

Second, if you did this without the understanding that now all the stuff that might fit "in-row" is now "in-row", which greatly...

IIRC, you...

Personally, I don't care for the use of HierarchyID nor it's particular form of positional notation.  It's also a bugger to fix if something goes wrong with it.

Perhaps the following...

To be sure, you don't need to rebuild ALL indexes... just the ones that became seriously fragmented because of the index-inversion the shrink.

I also recommend temporarily moving your largest indexes...

WHERE 
    D >= DATEADD(MINUTE, DATEDIFF(MINUTE, 0, GETDATE()) / 15 * 15, 0) AND
    D <  DATEADD(MINUTE, DATEDIFF(MINUTE, 0,...

What was it that worked for you?

You need to figure out a couple of things before you go off half-cocked doing something like that.  People need to "opt-in" and be informed that...

no matter whether you like it or not, 1)application developers write managed code more; 2)application developers implemented the functions, including FORMAT, CONVERT, CAST, LTRIM, RTRIM, TRIM, LEFT, RIGHT; 3)FORMAT...

I really hope this isn't a dumb question. Is there a reason other then style or preference as to why you'd do this:

IF @Var1...

😀

I think the default should be that you're automatically "opted out" unless you decide to "opt in".  Of course, that won't be profitable for these vendors and so that will...

true, format() is slower, and I started using it since it was not available until SQL server 2012 and my employer migrated from SQL 2008 to SQL 2016. Just...

Phil, I don't understand why you think you need the self-join in your code.  It can still be done in a single scan of the table.

 ...

I'm afraid that ROW_NUMBER() isn't going to hack it for this problem, Phil.  Here's what the output of your code is...

EDIT:  My...

• This reply was modified 5 years, 5 months ago by Jeff Moden. Reason: EDIT:  My apologies... I stand corrected!  I just saw the post that I didn't see before