asked
How can ORDER BY CURRENT_TIMESTAMP successfully implement paging?
I have seen quite a few examples of paging data in SQL Server 2005, that use something like the following: SELECT TOP(@N) ROW_NUMBER() OVER(ORDER BY CURRENT_TIMESTAMP) e.g. - [
http://weblogs.asp.net/fbouma/archive/2007/06/05/sqlserver-2005-paging-there-is-a-generic-wrapper-query-possible.aspx][3]- - [
http://www.yoursearchbuddy.com/custom-pagination-nhibernate][1] - [
http://ayende.com/blog/2334/paged-data-count-with-nhibernate-the-really-easy-way][2] Now when I run a query such as select 1, CURRENT_TIMESTAMP union all select 2, CURRENT_TIMESTAMP union all select 42, CURRENT_TIMESTAMP union all select 3, CURRENT_TIMESTAMP the CURRENT_TIMESTAMP seems to return entirely the same result. Given this, I don't see how an ORDER BY CURRENT_TIMESTAMP could possibly return a reliable paged dataset. However, since I have seen this used in multiple places (and in the case of NHibernate, a very widely used library), I find it difficult to believe that this approach is fundamentally flawed and I am the first one to see it! How is it that ORDER BY CURRENT_TIMESTAMP can produce reliable paging results? (or if it doesn't, under what scenarios does it give bad results?) [1]:
http://www.yoursearchbuddy.com/custom-pagination-nhibernate [2]:
http://ayende.com/blog/2334/paged-data-count-with-nhibernate-the-really-easy-way [3]:
http://weblogs.asp.net/fbouma/archive/2007/06/05/sqlserver-2005-paging-there-is-a-generic-wrapper-query-possible.aspx
All that **current\_timestamp** does is it returns the current date and time information, so it is pretty much identical to **getDate()**. The samples that you see in the referenced links use the **current\_timestamp** in order to avoid the sorting, not to actually implement it. The sorting in the samples is performed by the sub-select which has both **top(n)** and **order by** simply because it is not possible to have **order by** without **top(n)** or **top percent** in sub-selects. This technique makes the queries admittedly heavy, much heavier than they deserve to be but then again, one does not expect anything less than heavy out of such a heavy product like NHibernate. To apply the technique to your sample, you would have to use a select statement complemented with both top(n) and order by, make a sub-select out of it, select everything plus the row\_number() ordered by essentially nothing (that is why the current\_timestamp is used), and then finally make either a sub-select or the CTE out of that external select. This will make a query pretty heavy, but allow to to use a "generic template" which those links crave: ;with records as ( select *, -- note that the ordering is rubbished on purpose -- as the data is pre-sorted by that subselect below row_number() over (order by current_timestamp) N from (some_useful_pre_sorted_select_goes_gere) tmp ) select * from records where N between desired_range_here order by N; If you are to go this route, just ensure that your top(n) uses some ridiculously high value for that **n** in order to avoid undesired exclusion of some records in the useful, pre-sorted part. I have seen people choosing 2147483647 for that value. Since this is the highest integer value, it will ensure that top(n) returns all data that you have. Here is the sample of such "generic technique" but with **ntile** used in place of the row\_number. Suppose I want to return the third page of all records from sys.objects ordered by name and I want to have the records to be split into 10 pages (not that I will ever actually do it this way, there is no need to nest twice when once is already enough): declare @pages int; set @pages = 10; ;with records as ( select *, ntile(@pages) over(order by current_timestamp) PageNumber from ( select top (2147483647) * from sys.objects order by name ) tmp ) select * from records where PageNumber = 3; How I would actually do it is probably like this: ;with records as ( select *, ntile(@pages) over(order by name) PageNumber from sys.objects ) select * from records where PageNumber = 3; Generic? Sadly, it is not. Do I have to use the silly **top(ridiculous\_n)** and **order by** in the sub-select and useless ordering by current\_timestamp on the top? No. Does it make my query lighter? Absolutely. Oleg
Sorting the data by `CURRENT_TIMESTAMP` function has no sense. As `CURRENT_TIMESTAMP` is ANSI SQL compatible alias for the `GETDATE` function and this function is a run-time constant function. It means, that it's value is once determined by the query processor at the beginning of query execution and all uses inside the query uses the same value which was determined at the beginning. Conor Cunningham (one of the authors of the SQL Server query optimizer) has a blog post about the run-time constant functions: [Conor vs. Runtime Constant Functions][1] So in general ordering by a run-time constant function has no sense and will only add an overhead as all the record to which a run-time constant function value is added, have the same value. [1]:
http://blogs.msdn.com/b/conor_cunningham_msft/archive/2010/04/23/conor-vs-runtime-constant-functions.aspx
