Re: MS-SQL server LIMIT/OFFSET implementation

Thu, 20 Jul 2006 14:34:21 -0700

Hi Ivan,

I looked in the code and it seems that QuerySet class needs some serious refactoring to support MSSQL paging.
For SQL 2005 the SQL string should look like this:

WITH myTable AS
(SELECT fields, ROW_NUMBER() OVER (order by orderclause) AS RowNumber FROM table)
SELECT * FROM myTable
WHERE RowNumber between offset AND offset+limit

and for SQL 2000 should look like this:

SELECT fields FROM table
        WHERE primary key IN
            (SELECT TOP limit primary_key FROM table
                WHERE primary_key NOT IN
                    (SELECT TOP offset primary_key FROM table
                        WHERE filter_conditions
                        ORDER BY sort_field)
                AND filter_criteria
                ORDER BY sort_field)
        ORDER BY sort_field
       
And with join tables it looks completely crazy (I've only used it using DISTINCT with joins), but that's the optimal way to do.

A proper implementation should consider both cases since the ROW_NUMBER in SQL 2005 is the native implementation and it's a lot faster.
I'll start working on it although during the process I might need feedback from Django lead developers since I'm trying to refactor a core class.
I wouldn't want the patch to be rejected in the end.

Thanks,
Dan.


On 7/20/06, Ivan Sagalaev < [EMAIL PROTECTED]> wrote:

DanH wrote:
>
> Is someone working on MS-SQL support?
> I would like to help if possible with the implementation and testing.

Yes, testing is the main thing that holds it. The patch was developed
sporadically and as far as I remember all the theoretical problems were
known how to solve. It's just needed someone to dedicate the time to
test it.

P.S. I'm not the one of the core devs so it makes sense anyway to wait
an answer from Jacob or Adrian.


--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups "Django developers" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at http://groups.google.com/group/django-developers
-~----------~----~----~----~------~----~------~--~---

Reply via email to