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 used because you're planning for missing identity values due to deleted rows?
The cluster on the identity column is primarily for architectural reasons - as you know, you can never depend on IDENTITY columns to be contiguous, regardless of whether rows are ever deleted.
The ROW_NUMBER is just used to assign a sequential number in selected key order, and to make the selection of keys for the page we want easy.
The only reason I order by the underlying key (post_id) rather than the ROW_NUMBER (rn) is purely a limitation of the optimiser. We both know that the order of ROW_NUMBER assignment matches the ORDER BY of the ranking function, but the current optimiser does not include that logic. Sorting on the key avoids a small extra sort - so it's all rather technical, but then so are many things with SQL Server 🙂
Given that, I'd like to see the tests run with some random rows deleted, to better reflect a real-world scenario.
The full test rig is available for download in the Resources section. 😉
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi