Then follow the standard method and delete from the table in batches in clus key order.

In order to do that, you might first have to go thru the table and...

Well, if the datetime in the clus index is current datetime, you're ok.  Otherwise, not good, since you wouldn't be able to easily tell when copying the "good" rows that...

I got an email with just the initial q, I just quickly reviewed all the comments above, but my first thought was this:

;WITH ACTUALS AS
(
  SELECT...

Is the existing clustered composite key unique?  And explicitly declared to SQL as unique?

Yes, absolutely.  The test server has no idea what identity values are being assigned in prod and vice versa.

;WITH cte_dates AS (
    SELECT CAST(GETDATE() AS date) AS today,
        DATEPART(HOUR, GETDATE()) AS today_hour,
   ...

The code below generates SQL for from 1 to 20 @Tank entries, based on which ones were actually provided in the call to the proc, and then EXECs the code. ...

SELECT
    [Shelf Location], date /*, ...*/
FROM (
    SELECT *, DENSE_RANK() OVER(PARTITION BY [Shelf Location] ORDER BY date DESC) AS rank_num
 ...

I see no way to accurately parse that data will all the variations presented (and any number even worse than that that could happen).

If ID is (nearly) always an identity column, you can use ORDER BY $IDENTITY, that way you don't need to know the exact column name.

I'm not sure which specific "problem" you're referring...

It actually does explain it.  If SQL has to do a sort to get the rows in the correct order, then the cursor would be required to do a sort...

• This reply was modified 4 years, 1 months ago by ScottPletcher.

Sorry to have bothered you then.  Good luck with this.  (I believe if you changed your cursor to LOCAL FAST_FORWARD (as in standard in SQL Server unless you explicitly need...

You should change the index so that it has the proper sort order already, which will (should) avoid the sort in the query:

CREATE NONCLUSTERED INDEX [IX_ImageProduct_MID] ON...

The one...