Michelle Ufford's index rebuilding/defragging script might be of interest to you; it has great flexibility and handles LOBs correctly:

http://sqlfool.com/2010/04/index-defrag-script-v4-0/

Another way of doing it:

SELECT DV.Key1,

DV.MAX1,

DV.SUM2,

...

Thank you Jeff, Lutz, and Tom.

No pressure then 😉

Mark Stacey (4/26/2010)

---

I'd be interested to see the results of the covering index in parallel, just to set a benchmark. One day when I'm bored I'll take a stab...

jeremy.hutchinson (4/26/2010)

---

Whenever I'm testing the performance of something I will always try to do a number of test runs, as many as possible in the allowable time, and use the...

TheSQLGuru (4/26/2010)

---

I have done this type of thing for numerous clients, with stunning results as you show. Actually usually better, because they are almost always encountered in multi-table-open-ended search...

Thanks Wayne I appreciate it - especially since you know how much effort goes into writing these things!

Thanks Trey - I do...

pbarbin (4/26/2010)

---

Paul, great post and even better timing. We had an issue with paging on a larger table just last week and I've spent a fair amount of time...

sknox (4/26/2010)

---

Good article. One thing I noticed is that you're using a contiguous identity for your tests, but relying on ROW_NUMBER() for the paging. I'm assuming that ROW_NUMBER() is...

Thanks very much.

But I do have to dive into this particular one as I feel something odd is happening. The...

nabidavid (4/26/2010)

---

If you are not already covering it in a future article, I am curious if you have any advice on the best way (as in best performance) to incorporate...

That query plan highlights something I took out of the article at the last minute - to keep things simple.

You might wonder why SQL Server chooses a non-clustered index seek...

What he said 🙂

http://sqlfool.com/2010/04/index-defrag-script-v4-0/