Niiiicccceee!  I'm going to do some performance tests there because, although it's not a super common request, it's a request that has happened often enough...

The prompt was based on minimal code

this sql code omits a 2nd column with which we could order the rows.  Could you add a date...

@richard-2... this is one of the problems with not providing data in a readily consumable test table... everyone make up their own mind as to what the column names and...

I think the way to do this is to eliminate dupes while maintaining the order.  Then the Lead/Lag comparison of the difference between the previous point goes from positive and...

p.s.  I'm thinking that your good shot at it needs (as you imply) a little tweaking.  Try using the following data and see what I mean...

from (values (3),(10),(9),(8),(8),(8),(11),(12),(13),(12),(11),(6),(3),(2)) v(n)

Results:

While answer 3 may meet the overt requirements, it' returns the following "extra" info in the presence of NULL values, as well.

Warning: Null value is eliminated by...

Sometimes having dates, or parts of dates, as integers work well. But not often. A dimension can make sense, but in most transactional tables, I think dates ought to...

Calendar and Clock tables make life a lot easier when used as part of a Dimensional model.  Especially if it is implemented in a column store.

If you've seen the...

p.s.  You should also post the CREATE TABLE code for the table, its keys, and the indexes so we can see if you have possibilities going on such as "Trapped...

My first bit of advice on this is to move all but the last 13 months of data to an "Archive" database for the current database.  Keep it in the...

As a bit of a sidebar...

Even when GETDATE() was really the only show in town, I've never understood the propensity of people to make the terrible mistake of using DATE_IDs...

(ppts)

Please identify where in the article or this conversation where that...

Great question and even better explanation, Thomas.  Nicely done.

I never had any exposure to SQL Server 2000, but my Google-Fu tells me you'll need to use DBCC SHOWCONTIG (see SQL SERVER – Comparison Index Fragmentation,...