x

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.

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?)

[3]: http://weblogs.asp.net/fbouma/archive/2007/06/05/sqlserver-2005-paging-there-is-a-generic-wrapper-query-possible.aspx
more ▼

asked Aug 01, 2011 at 02:53 PM in Default

user-470 gravatar image

user-470
277 19 19 21

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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

more ▼

answered Aug 01, 2011 at 04:03 PM

Oleg gravatar image

Oleg
15.9k 2 4 24

(comments are locked)
10|1200 characters needed characters left

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
more ▼

answered Aug 02, 2011 at 12:58 AM

Pavel Pawlowski gravatar image

Pavel Pawlowski
22.3k 9 11 21

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1949
x11

asked: Aug 01, 2011 at 02:53 PM

Seen: 2154 times

Last Updated: Aug 01, 2011 at 02:53 PM