MSSQL Support

[Sean De La Torre]

I've been maintaining/enhancing a ticket
(http://code.djangoproject.com/ticket/2358) contributed by another
django user that adds MSSQL support to django. In addition to what
that user started, I've added full introspection capabilities and
patched a few bugs that I've found. I've been running a production
site using this patch for about a month now, and the MSSQL integration
seems to be stable.

I'd appreciate it if other MSSQL users could give the patch a try.
The one item missing from the ticket is paging, but MSSQL doesn't
support that natively, so any input regarding that problem would also
be most appreciated.

If the Django-users list is the more appropriate place for this
message, please let me know.

Thanks,

Sean

[DavidA]

What version of SQL Server have you been testing with? I think in 2005
there is support for paging. This brings up the question of how to
handle different versions of backends.

[Sean De La Torre]

I've been testing with SQL Server 2000 and MSDE. When I have more
time I intend to install SQL Server 2005 Express to see if there are
any issues with the newer versions.

[DavidA]

Sean De La Torre wrote:
> I've been testing with SQL Server 2000 and MSDE. When I have more
> time I intend to install SQL Server 2005 Express to see if there are
> any issues with the newer versions.
>
> On 10/21/06, DavidA <david.av...@gmail.com> wrote:
> >
> >
> > Sean De La Torre wrote:
> > > I've been maintaining/enhancing a ticket
> > > (http://code.djangoproject.com/ticket/2358) contributed by another
> > > django user that adds MSSQL support to django. In addition to what
> > > that user started, I've added full introspection capabilities and
> > > patched a few bugs that I've found. I've been running a production
> > > site using this patch for about a month now, and the MSSQL integration
> > > seems to be stable.
> > >
> > > I'd appreciate it if other MSSQL users could give the patch a try.
> > > The one item missing from the ticket is paging, but MSSQL doesn't
> > > support that natively, so any input regarding that problem would also
> > > be most appreciated.
> > >
> > > If the Django-users list is the more appropriate place for this
> > > message, please let me know.
> > >
> > > Thanks,
> >
> > What version of SQL Server have you been testing with? I think in 2005
> > there is support for paging. This brings up the question of how to
> > handle different versions of backends.
> >
> >
> > >
> >

For 2005 you can use the new ROW_NUMBER() function to help with
limit/offset:

SELECT * FROM (
SELECT ROW_NUMBER() OVER (ORDER BY field DESC)
AS Row, * FROM table)
AS foo
WHERE Row >= x AND Row <= y

For 2000 I've seen people use this approach, which works if your sort
field(s) are unique:

SELECT * FROM (
SELECT TOP x * FROM (
SELECT TOP y fieldlist
FROM table
WHERE conditions
ORDER BY field ASC) as foo
ORDER by field DESC) as bar
ORDER by field ASC

So to get records 81-100, y is 100 and x is 20, and the inner-most
select gets the top 100 rows. The middle select orders these in reverse
order and gets the top 20. The outer select reverses these again to put
them in the right order.

I'm not sure where if the db backends separate responsibilities enough
to allow you to wrap this up nicely in the SQL server backend. But it
might work.

[Sean De La Torre]

I'll take a look around and see if there is a proper place for these
statements. I had just come across the statement you posted for SQL
Server 2000, so we're probably looking in the same places to solve the
problem.