Scan = read of all of the pages of the index. If that's the cluster, it's every single page in the table. Seek = navigating directly to the rows needed...

Interesting. Going to have to play with this a bit...

Because order is not guaranteed unless you specify an order by. It may be the same this time, but that doesn't mean it always will.

If there's no order by,...

How often do you filter just by account? How often do you filter by just postdate?

Oh, and do you really need every single column in the table? If not, lose...

Aaahhhhh!!

Why does everyone recommend nolock any time there's a long running select? How about tuning the query, tuning the index and...

So does the code I gave you work or not? And if not, what's wrong with it?

Is that the only 'parse and compile' line? I just did my own test (with a stored proc and a clear plan cache), and got the following.

SQL Server parse and...

Welsh Corgi (1/13/2010)

---

I believe all that I need is an outer and an inner sub query with a SELECT TOP and an Order by clause to get the...

Will check tomorrow. Is late here.

Oh, agreed. Which is why, when these questions come up (and they're quite frequent), I'm always very pointed in asking what the definition of 'first record' is.

Jason Pociask (1/13/2010)

---

If you can afford index rebuilds as needed and/or enough free space in pages to handle a significant percentage of dispersed or non-increasing inserts then you could consider...

shannonjk (1/13/2010)

---

If you only want to return one row of data per account number, then why return the address or phone number or any other data? It would be kind...

That's one of...

Hard (near impossible) to say.

I suggest you set up a test database somewhere and try out both options. See how fast the archive and delete are with the cluster on...

ifila (1/13/2010)

---

What performance improvement might that provide?

BTW based on some initial performance testing with Gail's solution:

If i set TOP 60000 using her query it takes 8 seconds to get the...