Re: SELECT . . . ORDER BY - SQL 2000 vs. JET 4
From: Steve Kass (skass_at_drew.edu)
Date: 07/12/04
- Next message: Claudiney: "Re: Query performance"
- Previous message: Alvin Bruney [MVP]: "Re: SQL Server 2005 Express Beta messes up your SQL Server 2000, BEWARE"
- In reply to: Frank: "Re: SELECT . . . ORDER BY - SQL 2000 vs. JET 4"
- Next in thread: Frank: "Re: SELECT . . . ORDER BY - SQL 2000 vs. JET 4"
- Reply: Frank: "Re: SELECT . . . ORDER BY - SQL 2000 vs. JET 4"
- Reply: Frank: "Re: SELECT . . . ORDER BY - SQL 2000 vs. JET 4"
- Messages sorted by: [ date ] [ thread ]
Date: Sun, 11 Jul 2004 20:53:39 -0400
Frank,
As in my example, you can add
collate Latin1_General_CI_AI
in the ORDER BY clause to change the sort order. You can also declare
table columns with that same COLLATE clause (though I used the "wrong"
one below to imitate what is probably going on for you). If you declare
the table columns with COLLATE, then any indexes built on those columns
will respect the collation and can be used to optimize your queries. If
you do this, you do not need COLLATE in the ORDER BY clause. You can
also change the default collation for an entire database, which will
affect the collation applied to newly-declared tables:
ALTER DATABASE yourDatabase COLLATE Latin1_General_CI_AI
You can also specify the collation for an entire instance of SQL Server,
but only when the instance is first installed. If the instance
collation is different from the one for your database or particular
columns, the "wrong" collation will still be used for temporary tables
(unless you specify otherwise) and perhaps in other places that could
have an effect on performance.
SK
Frank wrote:
>Steve,
>
>There is no doubt how the 2 sort. While I'm not sure what they are called,
>it is documented as referred to in my first post. The KB article says
>Access/Jet cannot be changed, and you state that something can be changed,
>so it must be the SQL server (I'm using the free "desktop" version). How is
>this done? I have not been able to find any reference to how. Please be
>specific if you know.
>
>Thank you,
>
>Frank
>
>"Steve Kass" <skass@drew.edu> wrote in message
>news:uwhQBCrZEHA.3420@TK2MSFTNGP12.phx.gbl...
>
>
>>Frank,
>>
>> I suspect that Jet sorts according to Windows collation, whereas you
>>have the default SQL Server collation on your server (it's not a Windows
>>collation). If that's the case, you may be able to make the sorts match
>>by specifying the Windows collation on the Access/Jet box when you issue
>>the SQL Server query. Here's an example to show that the Windows
>>collation sorts like the KB article, but the SQL collation doesn't.
>>Basically it's the SQL collation that is behaving badly here, not the
>>Jet one...
>>
>>declare @t table (
>> k varchar(10) collate SQL_Latin1_General_Cp1_CI_AI
>>)
>>
>>insert into @t values('W-')
>>insert into @t values('W')
>>insert into @t values('W-B')
>>insert into @t values('WB')
>>
>>select k from @t
>>order by k
>>
>>select k from @t
>>order by k collate Latin1_General_CI_AI
>>
>>-- Steve Kass
>>-- Drew University
>>-- Ref: EEAA11F2-307A-42A6-AE7E-8F9B17029D16
>>
>>
>>
>>
>
>
>
>
>
- Next message: Claudiney: "Re: Query performance"
- Previous message: Alvin Bruney [MVP]: "Re: SQL Server 2005 Express Beta messes up your SQL Server 2000, BEWARE"
- In reply to: Frank: "Re: SELECT . . . ORDER BY - SQL 2000 vs. JET 4"
- Next in thread: Frank: "Re: SELECT . . . ORDER BY - SQL 2000 vs. JET 4"
- Reply: Frank: "Re: SELECT . . . ORDER BY - SQL 2000 vs. JET 4"
- Reply: Frank: "Re: SELECT . . . ORDER BY - SQL 2000 vs. JET 4"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
