Collation Order

How can I change the collation order for a msde db?
With SQL Server I use Rebuildm.exe utility, but with msde
that file is not present.
thanks,
AF
If your release doesnt contain a rebuildm.exe i am afraid that you will
have to setup Sql Server once again, or perhaps get a copy from one of your
non-MSDE SQL Servers (
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
<anonymous@.discussions.microsoft.com> schrieb im Newsbeitrag
news:0bcd01c54689$47bdc490$a401280a@.phx.gbl...
> How can I change the collation order for a msde db?
> With SQL Server I use Rebuildm.exe utility, but with msde
> that file is not present.
> thanks,
> AF
|||hi,
anonymous@.discussions.microsoft.com wrote:
> How can I change the collation order for a msde db?
> With SQL Server I use Rebuildm.exe utility, but with msde
> that file is not present.
> thanks,
> AF
yes, MSDE distributions do not include rebuildm.exe, but you can specify
alternate collation(s) for users' databases.. .. you can incur in some
overhead where different collations are involved in tempdb (as tempdb
inherits system collation), but this scenario is supported...
else you have to uninstall and reinstall MSDE with the desired collation
specifying the
COLLATION="selected_collation"
parameter at install time to the setup.exe boostrap installer (
http://msdn.microsoft.com/library/en...stsql_84xl.asp )
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.11.1 - DbaMgr ver 0.57.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
sqlsql

collation issue

Hi
I have a problem with collation orders as follows.
The server collation order is set to Latin1_General_CI_AS.
How ever a few databases, a small number, have the collation order of
SQL_Latin1_General_CI_AS.
Now, when some operations are requested from the interograting App,
involving tempDB temp via sprocs and functions, I get a collation error, no
surprise there.
I have tried DTS, opps not the right tool, and scripts. I have nearly got
there with the scripts, but not quite.
So, to the question. What is the best way to amend the collation order of
the 'rogue' databases. And are there any *foolproof* solns that would do
this without the need for Extensive regression testing?
TIA
ChrisIn your select try something along the lines of
Select ...
From ..
Inner join tab1.Col1 = Tab1.Col2 Collate Database_Default
Can't remember the exact syntax but you can tell your query to use the
coillation of of the current database.
"Chris" <.@.> wrote in message
news:DNv7f.3599$iZ4.1238@.newsfe2-gui.ntli.net...
> Hi
> I have a problem with collation orders as follows.
> The server collation order is set to Latin1_General_CI_AS.
> How ever a few databases, a small number, have the collation order of
> SQL_Latin1_General_CI_AS.
> Now, when some operations are requested from the interograting App,
> involving tempDB temp via sprocs and functions, I get a collation error,
> no surprise there.
> I have tried DTS, opps not the right tool, and scripts. I have nearly got
> there with the scripts, but not quite.
> So, to the question. What is the best way to amend the collation order of
> the 'rogue' databases. And are there any *foolproof* solns that would do
> this without the need for Extensive regression testing?
> TIA
> Chris
>

Collation

I have following collation setting in my current sql 7 server.
Locale ID = 33280
case sensitive
Sort Order Description
Character Set = 2, cp850
Code Page 850 (Multilingual) character set.
Sort Order = 40, bin_cp850
Binary Sort Order for the CodePage 850 Character Set
I plan to install sql 2000 on a new server and restore backup from sql 7.
What Locale ID and collation setting I should be using in sql2000.
Thanks
Select "SQL collations" at the collation selection dialog, then select
"Binary Order, for use with the CodePage 850 Character Set" (same sort
order that sp_helpsort reports for your 7.0 server).
Bart
Bart Duncan
Microsoft SQL Server Support
Please reply to the newsgroup only - thanks.
This posting is provided "AS IS" with no warranties, and confers no rights.
| Thread-Topic: Collation
| thread-index: AcSLbDA3HenK5wRzRMK500SXnoE2ZQ==
| X-WBNR-Posting-Host: 199.20.71.17
| From: "=?Utf-8?B?U0IxMzg=?=" <SB138@.discussions.microsoft.com>
| Subject: Collation
| Date: Thu, 26 Aug 2004 05:57:05 -0700
| Lines: 14
| Message-ID: <453BEDBF-EFD3-4F90-B477-547692531EB2@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.server
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
| Path: cpmsftngxa10.phx.gbl!TK2MSFTNGXA03.phx.gbl
| Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.server:356327
| X-Tomcat-NG: microsoft.public.sqlserver.server
|
| I have following collation setting in my current sql 7 server.
| Locale ID = 33280
| case sensitive
| Sort Order Description
|
| Character Set = 2, cp850
| Code Page 850 (Multilingual) character set.
| Sort Order = 40, bin_cp850
| Binary Sort Order for the CodePage 850 Character Set
|
| I plan to install sql 2000 on a new server and restore backup from sql 7.
| What Locale ID and collation setting I should be using in sql2000.
| Thanks
|
|
|||Hi Bart,
I select "Binary order, for use with the 850(Multilingual) character set"
sp_helpsort on sql 2000 gives following:
Latin1-General, binary sort for Unicode Data, SQL Server Sort Order 40 on
Code Page 850 for non-Unicode Data
Whereas in sql 7 installation it gives:
case sensitive
Sort Order Description
Character Set = 2, cp850
Code Page 850 (Multilingual) character set.
Sort Order = 40, bin_cp850
Binary Sort Order for the CodePage 850 Character Set
What I ma missing?
"Bart Duncan [MSFT]" wrote:

> Select "SQL collations" at the collation selection dialog, then select
> "Binary Order, for use with the CodePage 850 Character Set" (same sort
> order that sp_helpsort reports for your 7.0 server).
> Bart
> --
> Bart Duncan
> Microsoft SQL Server Support
> Please reply to the newsgroup only - thanks.
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> --
> | Thread-Topic: Collation
> | thread-index: AcSLbDA3HenK5wRzRMK500SXnoE2ZQ==
> | X-WBNR-Posting-Host: 199.20.71.17
> | From: "=?Utf-8?B?U0IxMzg=?=" <SB138@.discussions.microsoft.com>
> | Subject: Collation
> | Date: Thu, 26 Aug 2004 05:57:05 -0700
> | Lines: 14
> | Message-ID: <453BEDBF-EFD3-4F90-B477-547692531EB2@.microsoft.com>
> | MIME-Version: 1.0
> | Content-Type: text/plain;
> | charset="Utf-8"
> | Content-Transfer-Encoding: 7bit
> | X-Newsreader: Microsoft CDO for Windows 2000
> | Content-Class: urn:content-classes:message
> | Importance: normal
> | Priority: normal
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
> | Newsgroups: microsoft.public.sqlserver.server
> | NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
> | Path: cpmsftngxa10.phx.gbl!TK2MSFTNGXA03.phx.gbl
> | Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.server:356327
> | X-Tomcat-NG: microsoft.public.sqlserver.server
> |
> | I have following collation setting in my current sql 7 server.
> | Locale ID = 33280
> | case sensitive
> | Sort Order Description
> |
> | Character Set = 2, cp850
> | Code Page 850 (Multilingual) character set.
> | Sort Order = 40, bin_cp850
> | Binary Sort Order for the CodePage 850 Character Set
> |
> | I plan to install sql 2000 on a new server and restore backup from sql 7.
> | What Locale ID and collation setting I should be using in sql2000.
> | Thanks
> |
> |
>
|||sp_helpsort formats its output differently on the two versions.
Every collation specifies three things: (1) the sorting rules to use for
Unicode data, (2) the code page to use for non-Unicode data, and (3) the
sort order to use for non-Unicode data. Per your 7.0 sp_helpsort output
your 7.0 collation has the following properties:
1. Unicode sort = binary ("locale 33280" is binary)
2. Non-Unicode code page = 850
3. Non-Unicode sort order = 40
Your 2000 server has the same collation properties.
Try the backup and restore. Then run "sp_helpdb" to find the collation
name of the upgraded database, and SELECT SERVERPROPERTY('Collation') to
find the SQL 2000 server's collation name. They should be the same. If
they are not the same, reply back with the database's and SQL 2000 server's
collation names.
HTH,
Bart
Bart Duncan
Microsoft SQL Server Support
Please reply to the newsgroup only - thanks.
This posting is provided "AS IS" with no warranties, and confers no rights.
| Thread-Topic: Collation
| thread-index: AcSWm3Hjx0LhTQwxScuh1HZyoGdqpA==
| X-WBNR-Posting-Host: 199.20.71.17
| From: "=?Utf-8?B?U0IxMzg=?=" <SB138@.discussions.microsoft.com>
| References: <453BEDBF-EFD3-4F90-B477-547692531EB2@.microsoft.com>
<o8n2qv4iEHA.2968@.cpmsftngxa10.phx.gbl>
| Subject: RE: Collation
| Date: Thu, 9 Sep 2004 11:33:04 -0700
| Lines: 73
| Message-ID: <9FCF40ED-A3BB-4935-8486-DADDF4E04F61@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.server
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
| Path: cpmsftngxa10.phx.gbl!TK2MSFTNGXA03.phx.gbl
| Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.server:358302
| X-Tomcat-NG: microsoft.public.sqlserver.server
|
| Hi Bart,
| I select "Binary order, for use with the 850(Multilingual) character set"
| sp_helpsort on sql 2000 gives following:
| Latin1-General, binary sort for Unicode Data, SQL Server Sort Order 40 on
| Code Page 850 for non-Unicode Data
|
| Whereas in sql 7 installation it gives:
| case sensitive
| Sort Order Description
|
| Character Set = 2, cp850
| Code Page 850 (Multilingual) character set.
| Sort Order = 40, bin_cp850
| Binary Sort Order for the CodePage 850 Character Set
| What I ma missing?
|
| "Bart Duncan [MSFT]" wrote:
|
| > Select "SQL collations" at the collation selection dialog, then select
| > "Binary Order, for use with the CodePage 850 Character Set" (same sort
| > order that sp_helpsort reports for your 7.0 server).
| >
| > Bart
| > --
| > Bart Duncan
| > Microsoft SQL Server Support
| >
| > Please reply to the newsgroup only - thanks.
| > This posting is provided "AS IS" with no warranties, and confers no
rights.
| >
| >
| > --
| > | Thread-Topic: Collation
| > | thread-index: AcSLbDA3HenK5wRzRMK500SXnoE2ZQ==
| > | X-WBNR-Posting-Host: 199.20.71.17
| > | From: "=?Utf-8?B?U0IxMzg=?=" <SB138@.discussions.microsoft.com>
| > | Subject: Collation
| > | Date: Thu, 26 Aug 2004 05:57:05 -0700
| > | Lines: 14
| > | Message-ID: <453BEDBF-EFD3-4F90-B477-547692531EB2@.microsoft.com>
| > | MIME-Version: 1.0
| > | Content-Type: text/plain;
| > | charset="Utf-8"
| > | Content-Transfer-Encoding: 7bit
| > | X-Newsreader: Microsoft CDO for Windows 2000
| > | Content-Class: urn:content-classes:message
| > | Importance: normal
| > | Priority: normal
| > | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| > | Newsgroups: microsoft.public.sqlserver.server
| > | NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
| > | Path: cpmsftngxa10.phx.gbl!TK2MSFTNGXA03.phx.gbl
| > | Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.server:356327
| > | X-Tomcat-NG: microsoft.public.sqlserver.server
| > |
| > | I have following collation setting in my current sql 7 server.
| > | Locale ID = 33280
| > | case sensitive
| > | Sort Order Description
| >
| > |
| > | Character Set = 2, cp850
| > | Code Page 850 (Multilingual) character set.
| > | Sort Order = 40, bin_cp850
| > | Binary Sort Order for the CodePage 850 Character Set
| > |
| > | I plan to install sql 2000 on a new server and restore backup from
sql 7.
| > | What Locale ID and collation setting I should be using in sql2000.
| > | Thanks
| > |
| > |
| >
| >
|
|||Thanks for clarifying.
How I can find out what locale_id in 2K, and is it case sensitive or
insensitive? I just want to be sure that collation settings on 2000 are same
as 7 before I restore sql 7 backup onto sql 2K.
"Bart Duncan [MSFT]" wrote:

> sp_helpsort formats its output differently on the two versions.
> Every collation specifies three things: (1) the sorting rules to use for
> Unicode data, (2) the code page to use for non-Unicode data, and (3) the
> sort order to use for non-Unicode data. Per your 7.0 sp_helpsort output
> your 7.0 collation has the following properties:
> 1. Unicode sort = binary ("locale 33280" is binary)
> 2. Non-Unicode code page = 850
> 3. Non-Unicode sort order = 40
> Your 2000 server has the same collation properties.
> Try the backup and restore. Then run "sp_helpdb" to find the collation
> name of the upgraded database, and SELECT SERVERPROPERTY('Collation') to
> find the SQL 2000 server's collation name. They should be the same. If
> they are not the same, reply back with the database's and SQL 2000 server's
> collation names.
> HTH,
> Bart
> --
> Bart Duncan
> Microsoft SQL Server Support
> Please reply to the newsgroup only - thanks.
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> --
> | Thread-Topic: Collation
> | thread-index: AcSWm3Hjx0LhTQwxScuh1HZyoGdqpA==
> | X-WBNR-Posting-Host: 199.20.71.17
> | From: "=?Utf-8?B?U0IxMzg=?=" <SB138@.discussions.microsoft.com>
> | References: <453BEDBF-EFD3-4F90-B477-547692531EB2@.microsoft.com>
> <o8n2qv4iEHA.2968@.cpmsftngxa10.phx.gbl>
> | Subject: RE: Collation
> | Date: Thu, 9 Sep 2004 11:33:04 -0700
> | Lines: 73
> | Message-ID: <9FCF40ED-A3BB-4935-8486-DADDF4E04F61@.microsoft.com>
> | MIME-Version: 1.0
> | Content-Type: text/plain;
> | charset="Utf-8"
> | Content-Transfer-Encoding: 7bit
> | X-Newsreader: Microsoft CDO for Windows 2000
> | Content-Class: urn:content-classes:message
> | Importance: normal
> | Priority: normal
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
> | Newsgroups: microsoft.public.sqlserver.server
> | NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
> | Path: cpmsftngxa10.phx.gbl!TK2MSFTNGXA03.phx.gbl
> | Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.server:358302
> | X-Tomcat-NG: microsoft.public.sqlserver.server
> |
> | Hi Bart,
> | I select "Binary order, for use with the 850(Multilingual) character set"
> | sp_helpsort on sql 2000 gives following:
> | Latin1-General, binary sort for Unicode Data, SQL Server Sort Order 40 on
> | Code Page 850 for non-Unicode Data
> |
> | Whereas in sql 7 installation it gives:
> | case sensitive
> | Sort Order Description
> |
> | Character Set = 2, cp850
> | Code Page 850 (Multilingual) character set.
> | Sort Order = 40, bin_cp850
> | Binary Sort Order for the CodePage 850 Character Set
> | What I ma missing?
> |
> | "Bart Duncan [MSFT]" wrote:
> |
> | > Select "SQL collations" at the collation selection dialog, then select
> | > "Binary Order, for use with the CodePage 850 Character Set" (same sort
> | > order that sp_helpsort reports for your 7.0 server).
> | >
> | > Bart
> | > --
> | > Bart Duncan
> | > Microsoft SQL Server Support
> | >
> | > Please reply to the newsgroup only - thanks.
> | > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> | >
> | >
> | > --
> | > | Thread-Topic: Collation
> | > | thread-index: AcSLbDA3HenK5wRzRMK500SXnoE2ZQ==
> | > | X-WBNR-Posting-Host: 199.20.71.17
> | > | From: "=?Utf-8?B?U0IxMzg=?=" <SB138@.discussions.microsoft.com>
> | > | Subject: Collation
> | > | Date: Thu, 26 Aug 2004 05:57:05 -0700
> | > | Lines: 14
> | > | Message-ID: <453BEDBF-EFD3-4F90-B477-547692531EB2@.microsoft.com>
> | > | MIME-Version: 1.0
> | > | Content-Type: text/plain;
> | > | charset="Utf-8"
> | > | Content-Transfer-Encoding: 7bit
> | > | X-Newsreader: Microsoft CDO for Windows 2000
> | > | Content-Class: urn:content-classes:message
> | > | Importance: normal
> | > | Priority: normal
> | > | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
> | > | Newsgroups: microsoft.public.sqlserver.server
> | > | NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
> | > | Path: cpmsftngxa10.phx.gbl!TK2MSFTNGXA03.phx.gbl
> | > | Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.server:356327
> | > | X-Tomcat-NG: microsoft.public.sqlserver.server
> | > |
> | > | I have following collation setting in my current sql 7 server.
> | > | Locale ID = 33280
> | > | case sensitive
> | > | Sort Order Description
> | >
> | > |
> | > | Character Set = 2, cp850
> | > | Code Page 850 (Multilingual) character set.
> | > | Sort Order = 40, bin_cp850
> | > | Binary Sort Order for the CodePage 850 Character Set
> | > |
> | > | I plan to install sql 2000 on a new server and restore backup from
> sql 7.
> | > | What Locale ID and collation setting I should be using in sql2000.
> | > | Thanks
> | > |
> | > |
> | >
> | >
> |
>
|||SQL 7.0 lets you indepently specify different values for the three
properties of a collation. If you really want to you can set up a 7.0
server with totally inconsistent collation settings (for example, binary
non-Unicode sort order, Japanese non-Unicode code page, Greek case
insensitive Unicode sorting). In SQL 2000, though, the three collation
properties are no longer independently configurable; you just specify a
single collation, and that collation name implicitly determines the three
collation properties (Unicode sorting, non-Unicode sorting + non-Unicode
code page). The reason for this change in SQL 2000 is to try to ensure
that the handling of non-Unicode data and Unicode data are as consistent as
possible.
One side effect of this change is that SQL 2000's sp_helpsort output does
not report the three properties independently, in the same way that 7.0
does. In the 7.0 sp_helpsort output, Unicode sorting (this is called
"locale ID" in the 7.0 sp_helpsort) is identified explicitly by a specific
number, but in SQL 2000 this setting is only implied by the collation
identifier. In the 7.0 Books Online topic "Unicode Collation" you'll find
a list of locale IDs. This will tell you that 33280 is the binary Unicode
locale, which should be equivalent to "binary sort for Unicode Data" in the
SQL 2000 sp_helpsort output.
If you're thinking that this is a lot of trouble to go through just to
figure out what SQL 2000 setup settings you need to select to get a server
with the same collation settings as your 7.0 server, I agree; it's
unfortunately more complex than it could be. For what it's worth, there
have been some attempts to make improvements in this area in Yukon setup.
Bart
Bart Duncan
Microsoft SQL Server Support
Please reply to the newsgroup only - thanks.
This posting is provided "AS IS" with no warranties, and confers no rights.
| Thread-Topic: Collation
| thread-index: AcSZnOYevuO9yaKqSoifBdvA0OJFLQ==
| X-WBNR-Posting-Host: 199.20.71.17
| From: "=?Utf-8?B?U0IxMzg=?=" <SB138@.discussions.microsoft.com>
| References: <453BEDBF-EFD3-4F90-B477-547692531EB2@.microsoft.com>
<o8n2qv4iEHA.2968@.cpmsftngxa10.phx.gbl>
<9FCF40ED-A3BB-4935-8486-DADDF4E04F61@.microsoft.com>
<mrGZsi2lEHA.3608@.cpmsftngxa10.phx.gbl>
| Subject: RE: Collation
| Date: Mon, 13 Sep 2004 07:21:02 -0700
| Lines: 140
| Message-ID: <5BC4C012-F922-40D2-AC4F-0CC17429381B@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.server
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
| Path: cpmsftngxa10.phx.gbl!TK2MSFTNGXA03.phx.gbl
| Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.server:358661
| X-Tomcat-NG: microsoft.public.sqlserver.server
|
| Thanks for clarifying.
| How I can find out what locale_id in 2K, and is it case sensitive or
| insensitive? I just want to be sure that collation settings on 2000 are
same
| as 7 before I restore sql 7 backup onto sql 2K.
| "Bart Duncan [MSFT]" wrote:
|
| > sp_helpsort formats its output differently on the two versions.
| >
| > Every collation specifies three things: (1) the sorting rules to use
for
| > Unicode data, (2) the code page to use for non-Unicode data, and (3)
the
| > sort order to use for non-Unicode data. Per your 7.0 sp_helpsort
output
| > your 7.0 collation has the following properties:
| > 1. Unicode sort = binary ("locale 33280" is binary)
| > 2. Non-Unicode code page = 850
| > 3. Non-Unicode sort order = 40
| >
| > Your 2000 server has the same collation properties.
| >
| > Try the backup and restore. Then run "sp_helpdb" to find the collation
| > name of the upgraded database, and SELECT SERVERPROPERTY('Collation')
to
| > find the SQL 2000 server's collation name. They should be the same.
If
| > they are not the same, reply back with the database's and SQL 2000
server's
| > collation names.
| >
| > HTH,
| > Bart
| > --
| > Bart Duncan
| > Microsoft SQL Server Support
| >
| > Please reply to the newsgroup only - thanks.
| > This posting is provided "AS IS" with no warranties, and confers no
rights.
| >
| >
| > --
| > | Thread-Topic: Collation
| > | thread-index: AcSWm3Hjx0LhTQwxScuh1HZyoGdqpA==
| > | X-WBNR-Posting-Host: 199.20.71.17
| > | From: "=?Utf-8?B?U0IxMzg=?=" <SB138@.discussions.microsoft.com>
| > | References: <453BEDBF-EFD3-4F90-B477-547692531EB2@.microsoft.com>
| > <o8n2qv4iEHA.2968@.cpmsftngxa10.phx.gbl>
| > | Subject: RE: Collation
| > | Date: Thu, 9 Sep 2004 11:33:04 -0700
| > | Lines: 73
| > | Message-ID: <9FCF40ED-A3BB-4935-8486-DADDF4E04F61@.microsoft.com>
| > | MIME-Version: 1.0
| > | Content-Type: text/plain;
| > | charset="Utf-8"
| > | Content-Transfer-Encoding: 7bit
| > | X-Newsreader: Microsoft CDO for Windows 2000
| > | Content-Class: urn:content-classes:message
| > | Importance: normal
| > | Priority: normal
| > | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| > | Newsgroups: microsoft.public.sqlserver.server
| > | NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
| > | Path: cpmsftngxa10.phx.gbl!TK2MSFTNGXA03.phx.gbl
| > | Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.server:358302
| > | X-Tomcat-NG: microsoft.public.sqlserver.server
| > |
| > | Hi Bart,
| > | I select "Binary order, for use with the 850(Multilingual) character
set"
| > | sp_helpsort on sql 2000 gives following:
| > | Latin1-General, binary sort for Unicode Data, SQL Server Sort Order
40 on
| > | Code Page 850 for non-Unicode Data
| > |
| > | Whereas in sql 7 installation it gives:
| > | case sensitive
| > | Sort Order Description
| >
| > |
| > | Character Set = 2, cp850
| > | Code Page 850 (Multilingual) character set.
| > | Sort Order = 40, bin_cp850
| > | Binary Sort Order for the CodePage 850 Character Set
| > | What I ma missing?
| > |
| > | "Bart Duncan [MSFT]" wrote:
| > |
| > | > Select "SQL collations" at the collation selection dialog, then
select
| > | > "Binary Order, for use with the CodePage 850 Character Set" (same
sort
| > | > order that sp_helpsort reports for your 7.0 server).
| > | >
| > | > Bart
| > | > --
| > | > Bart Duncan
| > | > Microsoft SQL Server Support
| > | >
| > | > Please reply to the newsgroup only - thanks.
| > | > This posting is provided "AS IS" with no warranties, and confers no
| > rights.
| > | >
| > | >
| > | > --
| > | > | Thread-Topic: Collation
| > | > | thread-index: AcSLbDA3HenK5wRzRMK500SXnoE2ZQ==
| > | > | X-WBNR-Posting-Host: 199.20.71.17
| > | > | From: "=?Utf-8?B?U0IxMzg=?=" <SB138@.discussions.microsoft.com>
| > | > | Subject: Collation
| > | > | Date: Thu, 26 Aug 2004 05:57:05 -0700
| > | > | Lines: 14
| > | > | Message-ID: <453BEDBF-EFD3-4F90-B477-547692531EB2@.microsoft.com>
| > | > | MIME-Version: 1.0
| > | > | Content-Type: text/plain;
| > | > | charset="Utf-8"
| > | > | Content-Transfer-Encoding: 7bit
| > | > | X-Newsreader: Microsoft CDO for Windows 2000
| > | > | Content-Class: urn:content-classes:message
| > | > | Importance: normal
| > | > | Priority: normal
| > | > | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| > | > | Newsgroups: microsoft.public.sqlserver.server
| > | > | NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
| > | > | Path: cpmsftngxa10.phx.gbl!TK2MSFTNGXA03.phx.gbl
| > | > | Xref: cpmsftngxa10.phx.gbl
microsoft.public.sqlserver.server:356327
| > | > | X-Tomcat-NG: microsoft.public.sqlserver.server
| > | > |
| > | > | I have following collation setting in my current sql 7 server.
| > | > | Locale ID = 33280
| > | > | case sensitive
| > | > | Sort Order Description
| >
| > | >
| > | > |
| > | > | Character Set = 2, cp850
| > | > | Code Page 850 (Multilingual) character set.
| > | > | Sort Order = 40, bin_cp850
| > | > | Binary Sort Order for the CodePage 850 Character Set
| > | > |
| > | > | I plan to install sql 2000 on a new server and restore backup
from
| > sql 7.
| > | > | What Locale ID and collation setting I should be using in sql2000.
| > | > | Thanks
| > | > |
| > | > |
| > | >
| > | >
| > |
| >
| >
|

Collation

I have following collation setting in my current sql 7 server.
Locale ID = 33280
case sensitive
Sort Order Description
Character Set = 2, cp850
Code Page 850 (Multilingual) character set.
Sort Order = 40, bin_cp850
Binary Sort Order for the CodePage 850 Character Set
I plan to install sql 2000 on a new server and restore backup from sql 7.
What Locale ID and collation setting I should be using in sql2000.
ThanksSelect "SQL collations" at the collation selection dialog, then select
"Binary Order, for use with the CodePage 850 Character Set" (same sort
order that sp_helpsort reports for your 7.0 server).
Bart
--
Bart Duncan
Microsoft SQL Server Support
Please reply to the newsgroup only - thanks.
This posting is provided "AS IS" with no warranties, and confers no rights.
| Thread-Topic: Collation
| thread-index: AcSLbDA3HenK5wRzRMK500SXnoE2ZQ==
| X-WBNR-Posting-Host: 199.20.71.17
| From: "examnotes" <SB138@.discussions.microsoft.com>
| Subject: Collation
| Date: Thu, 26 Aug 2004 05:57:05 -0700
| Lines: 14
| Message-ID: <453BEDBF-EFD3-4F90-B477-547692531EB2@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.server
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
| Path: cpmsftngxa10.phx.gbl!TK2MSFTNGXA03.phx.gbl
| Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.server:356327
| X-Tomcat-NG: microsoft.public.sqlserver.server
|
| I have following collation setting in my current sql 7 server.
| Locale ID = 33280
| case sensitive
| Sort Order Description
|
| Character Set = 2, cp850
| Code Page 850 (Multilingual) character set.
| Sort Order = 40, bin_cp850
| Binary Sort Order for the CodePage 850 Character Set
|
| I plan to install sql 2000 on a new server and restore backup from sql 7.
| What Locale ID and collation setting I should be using in sql2000.
| Thanks
|
||||Hi Bart,
I select "Binary order, for use with the 850(Multilingual) character set"
sp_helpsort on sql 2000 gives following:
Latin1-General, binary sort for Unicode Data, SQL Server Sort Order 40 on
Code Page 850 for non-Unicode Data
Whereas in sql 7 installation it gives:
case sensitive
Sort Order Description
Character Set = 2, cp850
Code Page 850 (Multilingual) character set.
Sort Order = 40, bin_cp850
Binary Sort Order for the CodePage 850 Character Set
What I ma missing?
"Bart Duncan [MSFT]" wrote:

> Select "SQL collations" at the collation selection dialog, then select
> "Binary Order, for use with the CodePage 850 Character Set" (same sort
> order that sp_helpsort reports for your 7.0 server).
> Bart
> --
> Bart Duncan
> Microsoft SQL Server Support
> Please reply to the newsgroup only - thanks.
> This posting is provided "AS IS" with no warranties, and confers no rights
.
>
> --
> | Thread-Topic: Collation
> | thread-index: AcSLbDA3HenK5wRzRMK500SXnoE2ZQ==
> | X-WBNR-Posting-Host: 199.20.71.17
> | From: "examnotes" <SB138@.discussions.microsoft.com>
> | Subject: Collation
> | Date: Thu, 26 Aug 2004 05:57:05 -0700
> | Lines: 14
> | Message-ID: <453BEDBF-EFD3-4F90-B477-547692531EB2@.microsoft.com>
> | MIME-Version: 1.0
> | Content-Type: text/plain;
> | charset="Utf-8"
> | Content-Transfer-Encoding: 7bit
> | X-Newsreader: Microsoft CDO for Windows 2000
> | Content-Class: urn:content-classes:message
> | Importance: normal
> | Priority: normal
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
> | Newsgroups: microsoft.public.sqlserver.server
> | NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
> | Path: cpmsftngxa10.phx.gbl!TK2MSFTNGXA03.phx.gbl
> | Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.server:356327
> | X-Tomcat-NG: microsoft.public.sqlserver.server
> |
> | I have following collation setting in my current sql 7 server.
> | Locale ID = 33280
> | case sensitive
> | Sort Order Description
> |
> | Character Set = 2, cp850
> | Code Page 850 (Multilingual) character set.
> | Sort Order = 40, bin_cp850
> | Binary Sort Order for the CodePage 850 Character Set
> |
> | I plan to install sql 2000 on a new server and restore backup from sql 7
.
> | What Locale ID and collation setting I should be using in sql2000.
> | Thanks
> |
> |
>|||sp_helpsort formats its output differently on the two versions.
Every collation specifies three things: (1) the sorting rules to use for
Unicode data, (2) the code page to use for non-Unicode data, and (3) the
sort order to use for non-Unicode data. Per your 7.0 sp_helpsort output
your 7.0 collation has the following properties:
1. Unicode sort = binary ("locale 33280" is binary)
2. Non-Unicode code page = 850
3. Non-Unicode sort order = 40
Your 2000 server has the same collation properties.
Try the backup and restore. Then run "sp_helpdb" to find the collation
name of the upgraded database, and SELECT SERVERPROPERTY('Collation') to
find the SQL 2000 server's collation name. They should be the same. If
they are not the same, reply back with the database's and SQL 2000 server's
collation names.
HTH,
Bart
--
Bart Duncan
Microsoft SQL Server Support
Please reply to the newsgroup only - thanks.
This posting is provided "AS IS" with no warranties, and confers no rights.
| Thread-Topic: Collation
| thread-index: AcSWm3Hjx0LhTQwxScuh1HZyoGdqpA==
| X-WBNR-Posting-Host: 199.20.71.17
| From: "examnotes" <SB138@.discussions.microsoft.com>
| References: <453BEDBF-EFD3-4F90-B477-547692531EB2@.microsoft.com>
<o8n2qv4iEHA.2968@.cpmsftngxa10.phx.gbl>
| Subject: RE: Collation
| Date: Thu, 9 Sep 2004 11:33:04 -0700
| Lines: 73
| Message-ID: <9FCF40ED-A3BB-4935-8486-DADDF4E04F61@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.server
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
| Path: cpmsftngxa10.phx.gbl!TK2MSFTNGXA03.phx.gbl
| Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.server:358302
| X-Tomcat-NG: microsoft.public.sqlserver.server
|
| Hi Bart,
| I select "Binary order, for use with the 850(Multilingual) character set"
| sp_helpsort on sql 2000 gives following:
| Latin1-General, binary sort for Unicode Data, SQL Server Sort Order 40 on
| Code Page 850 for non-Unicode Data
|
| Whereas in sql 7 installation it gives:
| case sensitive
| Sort Order Description
|
| Character Set = 2, cp850
| Code Page 850 (Multilingual) character set.
| Sort Order = 40, bin_cp850
| Binary Sort Order for the CodePage 850 Character Set
| What I ma missing?
|
| "Bart Duncan [MSFT]" wrote:
|
| > Select "SQL collations" at the collation selection dialog, then select
| > "Binary Order, for use with the CodePage 850 Character Set" (same sort
| > order that sp_helpsort reports for your 7.0 server).
| >
| > Bart
| > --
| > Bart Duncan
| > Microsoft SQL Server Support
| >
| > Please reply to the newsgroup only - thanks.
| > This posting is provided "AS IS" with no warranties, and confers no
rights.
| >
| >
| > --
| > | Thread-Topic: Collation
| > | thread-index: AcSLbDA3HenK5wRzRMK500SXnoE2ZQ==
| > | X-WBNR-Posting-Host: 199.20.71.17
| > | From: "examnotes" <SB138@.discussions.microsoft.com>
| > | Subject: Collation
| > | Date: Thu, 26 Aug 2004 05:57:05 -0700
| > | Lines: 14
| > | Message-ID: <453BEDBF-EFD3-4F90-B477-547692531EB2@.microsoft.com>
| > | MIME-Version: 1.0
| > | Content-Type: text/plain;
| > | charset="Utf-8"
| > | Content-Transfer-Encoding: 7bit
| > | X-Newsreader: Microsoft CDO for Windows 2000
| > | Content-Class: urn:content-classes:message
| > | Importance: normal
| > | Priority: normal
| > | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| > | Newsgroups: microsoft.public.sqlserver.server
| > | NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
| > | Path: cpmsftngxa10.phx.gbl!TK2MSFTNGXA03.phx.gbl
| > | Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.server:356327
| > | X-Tomcat-NG: microsoft.public.sqlserver.server
| > |
| > | I have following collation setting in my current sql 7 server.
| > | Locale ID = 33280
| > | case sensitive
| > | Sort Order Description
| >
| > |
| > | Character Set = 2, cp850
| > | Code Page 850 (Multilingual) character set.
| > | Sort Order = 40, bin_cp850
| > | Binary Sort Order for the CodePage 850 Character Set
| > |
| > | I plan to install sql 2000 on a new server and restore backup from
sql 7.
| > | What Locale ID and collation setting I should be using in sql2000.
| > | Thanks
| > |
| > |
| >
| >
||||Thanks for clarifying.
How I can find out what locale_id in 2K, and is it case sensitive or
insensitive? I just want to be sure that collation settings on 2000 are same
as 7 before I restore sql 7 backup onto sql 2K.
"Bart Duncan [MSFT]" wrote:

> sp_helpsort formats its output differently on the two versions.
> Every collation specifies three things: (1) the sorting rules to use for
> Unicode data, (2) the code page to use for non-Unicode data, and (3) the
> sort order to use for non-Unicode data. Per your 7.0 sp_helpsort output
> your 7.0 collation has the following properties:
> 1. Unicode sort = binary ("locale 33280" is binary)
> 2. Non-Unicode code page = 850
> 3. Non-Unicode sort order = 40
> Your 2000 server has the same collation properties.
> Try the backup and restore. Then run "sp_helpdb" to find the collation
> name of the upgraded database, and SELECT SERVERPROPERTY('Collation') to
> find the SQL 2000 server's collation name. They should be the same. If
> they are not the same, reply back with the database's and SQL 2000 server'
s
> collation names.
> HTH,
> Bart
> --
> Bart Duncan
> Microsoft SQL Server Support
> Please reply to the newsgroup only - thanks.
> This posting is provided "AS IS" with no warranties, and confers no rights
.
>
> --
> | Thread-Topic: Collation
> | thread-index: AcSWm3Hjx0LhTQwxScuh1HZyoGdqpA==
> | X-WBNR-Posting-Host: 199.20.71.17
> | From: "examnotes" <SB138@.discussions.microsoft.com>
> | References: <453BEDBF-EFD3-4F90-B477-547692531EB2@.microsoft.com>
> <o8n2qv4iEHA.2968@.cpmsftngxa10.phx.gbl>
> | Subject: RE: Collation
> | Date: Thu, 9 Sep 2004 11:33:04 -0700
> | Lines: 73
> | Message-ID: <9FCF40ED-A3BB-4935-8486-DADDF4E04F61@.microsoft.com>
> | MIME-Version: 1.0
> | Content-Type: text/plain;
> | charset="Utf-8"
> | Content-Transfer-Encoding: 7bit
> | X-Newsreader: Microsoft CDO for Windows 2000
> | Content-Class: urn:content-classes:message
> | Importance: normal
> | Priority: normal
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
> | Newsgroups: microsoft.public.sqlserver.server
> | NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
> | Path: cpmsftngxa10.phx.gbl!TK2MSFTNGXA03.phx.gbl
> | Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.server:358302
> | X-Tomcat-NG: microsoft.public.sqlserver.server
> |
> | Hi Bart,
> | I select "Binary order, for use with the 850(Multilingual) character se
t"
> | sp_helpsort on sql 2000 gives following:
> | Latin1-General, binary sort for Unicode Data, SQL Server Sort Order 40 o
n
> | Code Page 850 for non-Unicode Data
> |
> | Whereas in sql 7 installation it gives:
> | case sensitive
> | Sort Order Description
> |
> | Character Set = 2, cp850
> | Code Page 850 (Multilingual) character set.
> | Sort Order = 40, bin_cp850
> | Binary Sort Order for the CodePage 850 Character Set
> | What I ma missing?
> |
> | "Bart Duncan [MSFT]" wrote:
> |
> | > Select "SQL collations" at the collation selection dialog, then select
> | > "Binary Order, for use with the CodePage 850 Character Set" (same sort
> | > order that sp_helpsort reports for your 7.0 server).
> | >
> | > Bart
> | > --
> | > Bart Duncan
> | > Microsoft SQL Server Support
> | >
> | > Please reply to the newsgroup only - thanks.
> | > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> | >
> | >
> | > --
> | > | Thread-Topic: Collation
> | > | thread-index: AcSLbDA3HenK5wRzRMK500SXnoE2ZQ==
> | > | X-WBNR-Posting-Host: 199.20.71.17
> | > | From: "examnotes" <SB138@.discussions.microsoft.com>
> | > | Subject: Collation
> | > | Date: Thu, 26 Aug 2004 05:57:05 -0700
> | > | Lines: 14
> | > | Message-ID: <453BEDBF-EFD3-4F90-B477-547692531EB2@.microsoft.com>
> | > | MIME-Version: 1.0
> | > | Content-Type: text/plain;
> | > | charset="Utf-8"
> | > | Content-Transfer-Encoding: 7bit
> | > | X-Newsreader: Microsoft CDO for Windows 2000
> | > | Content-Class: urn:content-classes:message
> | > | Importance: normal
> | > | Priority: normal
> | > | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
> | > | Newsgroups: microsoft.public.sqlserver.server
> | > | NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
> | > | Path: cpmsftngxa10.phx.gbl!TK2MSFTNGXA03.phx.gbl
> | > | Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.server:356327
> | > | X-Tomcat-NG: microsoft.public.sqlserver.server
> | > |
> | > | I have following collation setting in my current sql 7 server.
> | > | Locale ID = 33280
> | > | case sensitive
> | > | Sort Order Description
> | >
> | > |
> | > | Character Set = 2, cp850
> | > | Code Page 850 (Multilingual) character set.
> | > | Sort Order = 40, bin_cp850
> | > | Binary Sort Order for the CodePage 850 Character Set
> | > |
> | > | I plan to install sql 2000 on a new server and restore backup from
> sql 7.
> | > | What Locale ID and collation setting I should be using in sql2000.
> | > | Thanks
> | > |
> | > |
> | >
> | >
> |
>|||SQL 7.0 lets you indepently specify different values for the three
properties of a collation. If you really want to you can set up a 7.0
server with totally inconsistent collation settings (for example, binary
non-Unicode sort order, Japanese non-Unicode code page, Greek case
insensitive Unicode sorting). In SQL 2000, though, the three collation
properties are no longer independently configurable; you just specify a
single collation, and that collation name implicitly determines the three
collation properties (Unicode sorting, non-Unicode sorting + non-Unicode
code page). The reason for this change in SQL 2000 is to try to ensure
that the handling of non-Unicode data and Unicode data are as consistent as
possible.
One side effect of this change is that SQL 2000's sp_helpsort output does
not report the three properties independently, in the same way that 7.0
does. In the 7.0 sp_helpsort output, Unicode sorting (this is called
"locale ID" in the 7.0 sp_helpsort) is identified explicitly by a specific
number, but in SQL 2000 this setting is only implied by the collation
identifier. In the 7.0 Books Online topic "Unicode Collation" you'll find
a list of locale IDs. This will tell you that 33280 is the binary Unicode
locale, which should be equivalent to "binary sort for Unicode Data" in the
SQL 2000 sp_helpsort output.
If you're thinking that this is a lot of trouble to go through just to
figure out what SQL 2000 setup settings you need to select to get a server
with the same collation settings as your 7.0 server, I agree; it's
unfortunately more complex than it could be. For what it's worth, there
have been some attempts to make improvements in this area in Yukon setup.
Bart
--
Bart Duncan
Microsoft SQL Server Support
Please reply to the newsgroup only - thanks.
This posting is provided "AS IS" with no warranties, and confers no rights.
| Thread-Topic: Collation
| thread-index: AcSZnOYevuO9yaKqSoifBdvA0OJFLQ==
| X-WBNR-Posting-Host: 199.20.71.17
| From: "examnotes" <SB138@.discussions.microsoft.com>
| References: <453BEDBF-EFD3-4F90-B477-547692531EB2@.microsoft.com>
<o8n2qv4iEHA.2968@.cpmsftngxa10.phx.gbl>
<9FCF40ED-A3BB-4935-8486-DADDF4E04F61@.microsoft.com>
<mrGZsi2lEHA.3608@.cpmsftngxa10.phx.gbl>
| Subject: RE: Collation
| Date: Mon, 13 Sep 2004 07:21:02 -0700
| Lines: 140
| Message-ID: <5BC4C012-F922-40D2-AC4F-0CC17429381B@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.server
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
| Path: cpmsftngxa10.phx.gbl!TK2MSFTNGXA03.phx.gbl
| Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.server:358661
| X-Tomcat-NG: microsoft.public.sqlserver.server
|
| Thanks for clarifying.
| How I can find out what locale_id in 2K, and is it case sensitive or
| insensitive? I just want to be sure that collation settings on 2000 are
same
| as 7 before I restore sql 7 backup onto sql 2K.
| "Bart Duncan [MSFT]" wrote:
|
| > sp_helpsort formats its output differently on the two versions.
| >
| > Every collation specifies three things: (1) the sorting rules to use
for
| > Unicode data, (2) the code page to use for non-Unicode data, and (3)
the
| > sort order to use for non-Unicode data. Per your 7.0 sp_helpsort
output
| > your 7.0 collation has the following properties:
| > 1. Unicode sort = binary ("locale 33280" is binary)
| > 2. Non-Unicode code page = 850
| > 3. Non-Unicode sort order = 40
| >
| > Your 2000 server has the same collation properties.
| >
| > Try the backup and restore. Then run "sp_helpdb" to find the collation
| > name of the upgraded database, and SELECT SERVERPROPERTY('Collation')
to
| > find the SQL 2000 server's collation name. They should be the same.
If
| > they are not the same, reply back with the database's and SQL 2000
server's
| > collation names.
| >
| > HTH,
| > Bart
| > --
| > Bart Duncan
| > Microsoft SQL Server Support
| >
| > Please reply to the newsgroup only - thanks.
| > This posting is provided "AS IS" with no warranties, and confers no
rights.
| >
| >
| > --
| > | Thread-Topic: Collation
| > | thread-index: AcSWm3Hjx0LhTQwxScuh1HZyoGdqpA==
| > | X-WBNR-Posting-Host: 199.20.71.17
| > | From: "examnotes" <SB138@.discussions.microsoft.com>
| > | References: <453BEDBF-EFD3-4F90-B477-547692531EB2@.microsoft.com>
| > <o8n2qv4iEHA.2968@.cpmsftngxa10.phx.gbl>
| > | Subject: RE: Collation
| > | Date: Thu, 9 Sep 2004 11:33:04 -0700
| > | Lines: 73
| > | Message-ID: <9FCF40ED-A3BB-4935-8486-DADDF4E04F61@.microsoft.com>
| > | MIME-Version: 1.0
| > | Content-Type: text/plain;
| > | charset="Utf-8"
| > | Content-Transfer-Encoding: 7bit
| > | X-Newsreader: Microsoft CDO for Windows 2000
| > | Content-Class: urn:content-classes:message
| > | Importance: normal
| > | Priority: normal
| > | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| > | Newsgroups: microsoft.public.sqlserver.server
| > | NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
| > | Path: cpmsftngxa10.phx.gbl!TK2MSFTNGXA03.phx.gbl
| > | Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.server:358302
| > | X-Tomcat-NG: microsoft.public.sqlserver.server
| > |
| > | Hi Bart,
| > | I select "Binary order, for use with the 850(Multilingual) character
set"
| > | sp_helpsort on sql 2000 gives following:
| > | Latin1-General, binary sort for Unicode Data, SQL Server Sort Order
40 on
| > | Code Page 850 for non-Unicode Data
| > |
| > | Whereas in sql 7 installation it gives:
| > | case sensitive
| > | Sort Order Description
| >
| > |
| > | Character Set = 2, cp850
| > | Code Page 850 (Multilingual) character set.
| > | Sort Order = 40, bin_cp850
| > | Binary Sort Order for the CodePage 850 Character Set
| > | What I ma missing?
| > |
| > | "Bart Duncan [MSFT]" wrote:
| > |
| > | > Select "SQL collations" at the collation selection dialog, then
select
| > | > "Binary Order, for use with the CodePage 850 Character Set" (same
sort
| > | > order that sp_helpsort reports for your 7.0 server).
| > | >
| > | > Bart
| > | > --
| > | > Bart Duncan
| > | > Microsoft SQL Server Support
| > | >
| > | > Please reply to the newsgroup only - thanks.
| > | > This posting is provided "AS IS" with no warranties, and confers no
| > rights.
| > | >
| > | >
| > | > --
| > | > | Thread-Topic: Collation
| > | > | thread-index: AcSLbDA3HenK5wRzRMK500SXnoE2ZQ==
| > | > | X-WBNR-Posting-Host: 199.20.71.17
| > | > | From: "examnotes" <SB138@.discussions.microsoft.com>
| > | > | Subject: Collation
| > | > | Date: Thu, 26 Aug 2004 05:57:05 -0700
| > | > | Lines: 14
| > | > | Message-ID: <453BEDBF-EFD3-4F90-B477-547692531EB2@.microsoft.com>
| > | > | MIME-Version: 1.0
| > | > | Content-Type: text/plain;
| > | > | charset="Utf-8"
| > | > | Content-Transfer-Encoding: 7bit
| > | > | X-Newsreader: Microsoft CDO for Windows 2000
| > | > | Content-Class: urn:content-classes:message
| > | > | Importance: normal
| > | > | Priority: normal
| > | > | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| > | > | Newsgroups: microsoft.public.sqlserver.server
| > | > | NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
| > | > | Path: cpmsftngxa10.phx.gbl!TK2MSFTNGXA03.phx.gbl
| > | > | Xref: cpmsftngxa10.phx.gbl
microsoft.public.sqlserver.server:356327
| > | > | X-Tomcat-NG: microsoft.public.sqlserver.server
| > | > |
| > | > | I have following collation setting in my current sql 7 server.
| > | > | Locale ID = 33280
| > | > | case sensitive
| > | > | Sort Order Description
| >
| > | >
| > | > |
| > | > | Character Set = 2, cp850
| > | > | Code Page 850 (Multilingual) character set.
| > | > | Sort Order = 40, bin_cp850
| > | > | Binary Sort Order for the CodePage 850 Character Set
| > | > |
| > | > | I plan to install sql 2000 on a new server and restore backup
from
| > sql 7.
| > | > | What Locale ID and collation setting I should be using in sql2000.
| > | > | Thanks
| > | > |
| > | > |
| > | >
| > | >
| > |
| >
| >
|

Collapse all details in SSRS 2005 report?

Hello,

I have a report that contains a single group for customers. Each customer order is placed as a detail record under the group. I have the group header setup to toggle the detail records (so if a user clicks the plus sign image, the details are shown, if the user clicks the minus image, the details are hidden). By default when the report opens up the details are shown.

Rather than having the user click through each customer row to collapse all the details, is it possible to place a "collapse all" image or control somewhere on the report that will handle the collapsing of all the detail rows for each customer record? I assume if there is a way, there is something similar for expanding all as well?

Thanks!

The "initial state" of toggles on groups can be determined by expressions. You could add e.g. a boolean report parameter and then set the initial toggle state of all toggles to be based on the report parameter value.

-- Robert

Code Problem - Updating parameter

I want to update a parameter in order to know whether a certain type of row
has been printed so that I can suppress it later. the code below generates
an error " Reference to a non-shared member requires an object reference" .
How do I pass in /reference my parameter so that I can set the value? Any
help greatly appreciated
Public Function PrintCensus() As Integer
if Parameters!PrintCensus.Value = 0 Then
Parameters!PrintCensus.Value = 1
Return 0
end if
return 1
End FunctionWhat you have to do is:
1. Define your function as:
> Public Function PrintCensus(PrintCensusValue as integer) As Integer
>
> if PrintCensusValue= 0 Then
> Return 1
> else
Return 0
> end if
> End Function
2. Call your function from expresion field as:
=Code.PrintCensus(Parameters!PrintCensus.Value)
Hope this helps,
Mónica
"Peter Feakins" <PeterFeakins@.discussions.microsoft.com> escribió en el
mensaje news:71D8DF3A-A67F-41CB-8B8B-CE71E0FF63E4@.microsoft.com...
>I want to update a parameter in order to know whether a certain type of row
> has been printed so that I can suppress it later. the code below
> generates
> an error " Reference to a non-shared member requires an object reference"
> .
> How do I pass in /reference my parameter so that I can set the value? Any
> help greatly appreciated
> Public Function PrintCensus() As Integer
> if Parameters!PrintCensus.Value = 0 Then
> Parameters!PrintCensus.Value = 1
> Return 0
> end if
> return 1
> End Function|||apparently you cannot change the value of a parameter after it is set prior
to report execution.
"Peter Feakins" wrote:
> I want to update a parameter in order to know whether a certain type of row
> has been printed so that I can suppress it later. the code below generates
> an error " Reference to a non-shared member requires an object reference" .
> How do I pass in /reference my parameter so that I can set the value? Any
> help greatly appreciated
> Public Function PrintCensus() As Integer
> if Parameters!PrintCensus.Value = 0 Then
> Parameters!PrintCensus.Value = 1
> Return 0
> end if
> return 1
> End Function

Code Box

I want to set a variable so i'll know if I printed census information
already so that I can suppress it later. something on the order of the
function below. I"m getting an error "Reference to a non-shared member
requires an object reference". I'm not sure if I need to passs theobject in
or how to reference it. any help is appreciated.
Public Function PrintCensus( ) As Integer
if Parameters!PrintCensus.Value = 0 Then
Parameters!PrintCensus.Value = 0
Return 0
end if
return 1
End Functioni aplogize for the double post - waited so long for this to show up i assumed
that I had clicked close instead
"Peter Feakins" wrote:
> I want to set a variable so i'll know if I printed census information
> already so that I can suppress it later. something on the order of the
> function below. I"m getting an error "Reference to a non-shared member
> requires an object reference". I'm not sure if I need to passs theobject in
> or how to reference it. any help is appreciated.
> Public Function PrintCensus( ) As Integer
> if Parameters!PrintCensus.Value = 0 Then
> Parameters!PrintCensus.Value = 0
> Return 0
> end if
> return 1
> End Function|||On Jan 22, 8:32 pm, Peter Feakins
<PeterFeak...@.discussions.microsoft.com> wrote:
> i aplogize for the double post - waited so long for this to show up i assumed
> that I had clicked close instead
> "Peter Feakins" wrote:
> > I want to set a variable so i'll know if I printed census information
> > already so that I can suppress it later. something on the order of the
> > function below. I"m getting an error "Reference to a non-shared member
> > requires an object reference". I'm not sure if I need to passs theobject in
> > or how to reference it. any help is appreciated.
> > Public Function PrintCensus( ) As Integer
> > if Parameters!PrintCensus.Value = 0 Then
> > Parameters!PrintCensus.Value = 0
> > Return 0
> > end if
> > return 1
> > End Function
If this function is a part of the Code section of a report, you will
most likely need to pass the parameter PrintCensus ByVal to the
function. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant

coalesce does not seem to work

Hi,

I have the following table with some sample values, I want to return the first non null value in that order. COALESCE does not seem to work for me, it does not return the 3rd record. I need to include this in my select statement. Any urgent help please.

Mobile Business Private

NULL 345 NULL
4646 65464 65765
NULL 564
654654 564 6546

I want the following as my results:

Number

345
4646
564
654654

Select COALESCE(Mobile,Business,Private) as Number from Table returns:

345
4646

654654

(this is a test to see if private returns & it did with is not null but then how do i include in my select statement to show any one of the 3 fields)

select mobile,business,private where private is not null returns:

65765
564
6546

thanks

As you mentioned, COALESCE returns the first Non NULL value. You result is not what you want but the COALESCE is correct. You have a blank cell in your table. It is not NULL. You can use a CASE statement to check either NULL or blank to get the result you want. Or you can make sure your missing value cells are NULL.

HTH.

Clustering/Failover

We're looking at moving to the newest version of SQL when it's released. In
order to take advantage of the clustering capabilities of SQL, do we also
have to cluster at the OS level, or can you have a SQL cluster running on two
stand-alone Windows Servers?
Thanks!Molnir,
Might take a look at:
http://www.databasejournal.com/features/mssql/article.php/3444181
and
High Availability and Failover Clustering at:
http://www.microsoft.com/technet/prodtechnol/sql/themes/high-availability.mspx
HTH
Jerry
"Molnir" <Molnir@.discussions.microsoft.com> wrote in message
news:B171CF4B-BC63-46D9-88DF-D8F855581A8B@.microsoft.com...
> We're looking at moving to the newest version of SQL when it's released.
> In
> order to take advantage of the clustering capabilities of SQL, do we also
> have to cluster at the OS level, or can you have a SQL cluster running on
> two
> stand-alone Windows Servers?
> Thanks!|||You might save yourself some frustration and also look at Neverfail
(www.neverfialgroup.com)
Jo
Molnir wrote:
> We're looking at moving to the newest version of SQL when it's released. In
> order to take advantage of the clustering capabilities of SQL, do we also
> have to cluster at the OS level, or can you have a SQL cluster running on two
> stand-alone Windows Servers?
> Thanks!

Clustering Resource depencies

Hello,

With SQL2000 configured with MSCS, we had to stop the SQL service to add additional disk in the SQL Cluster group (in order to use these disks for databases for example).. this was due to the dependencies between the resources.
Do we still have the same issue with SQL2005 running on the cluster? Does the new mountpoint support help in this scenario?
Thanks
Jerome

Yes, the same issue does exist with SQL Server 2005 when adding disks. The new mountpoint support does not really help in this scenario as the mountpoint disk should also be added as a dependent disk to the resource group. It is possible to add the mountpoint without adding the disk to the resource group, but in that case the loss of the disk would not be detected by the cluster service; the SQL Server would attempt to continue running even without the disk (and mountpoint).

Thanks,

Don

clustering differences windows 2003

I found several great step by step articles on clustering in windows 2000.
What do I need to change in order to work with windows 2003? I read the
previous posts and it seems the setup is almost identical.
Thanks for your help
Andrew
Here is an articles on what's new:
http://www.microsoft.com/windowsserv...lustering.mspx
If you understand Windows 2000 clustering, you should be good to go.
Cheers,
Rod
MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering
http://www.msmvps.com/clustering - Blog
"Andrew" <Andrew@.discussions.microsoft.com> wrote in message
news:CCD04AA7-21C7-4650-B7DC-E254ABB19E33@.microsoft.com...
>I found several great step by step articles on clustering in windows 2000.
> What do I need to change in order to work with windows 2003? I read the
> previous posts and it seems the setup is almost identical.
> Thanks for your help
> Andrew
|||Hi Andrew,
Although most of the things are same for SQL Server 2000 Failover clustering on Win2K3, here are some things that are different/unsupported:
Installation of a Named Instance of SQL Server 2000 Virtual Server on a Windows 2003-Based Cluster Fails
http://support.microssoft.com/?id=815431
SQL Server 2000 Cluster Does Not Install on Windows Server 2003-Based Computers Where Terminal Services Is Installed
http://support.microsoft.com/?id=301520
Virtual SQL Server 2000 Installation Fails on Cluster That Has Eight Nodes
http://support.microsoft.com/?id=811054
SQL Server 2000 support for mounted volumes
http://support.microsoft.com/?id=819546
How to Configure Microsoft Distributed Transaction Coordinator on a Windows Server 2003 Cluster
http://support.microsoft.com/?id=301600
How to enable Network DTC access in Windows Server 2003
http://support.microsoft.com/?kbid=817064
HTH,
Best Regards,
Uttam Parui
Microsoft Corporation
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection Program and to order your FREE Security Tool Kit, please visit http://www.microsoft.com/security.
Microsoft highly recommends that users with Internet access update their Microsoft software to better protect against viruses and security vulnerabilities. The easiest way to do this is to visit the following websites:
http://www.microsoft.com/protect
http://www.microsoft.com/security/guidance/default.mspx

Clustered Indexes and physical order.

I've got a question about how clustered indexes map to the physical order of
the records in a database. I've always been under the impression that a
clustered index orders the records physically in the order of the clustered
key. As an MCT, I've taught this idea many times.
However, on a whim I just did a page dump of a table, and noticed that the
records were not in the order of the clustered key (I used the authors table
in the pubs database). An an aside, a "select *" does display them in
clustered key order; Also the column order in a "select *" does not match
the column order in the page dump.
From a practical point of view, I suppose this makes sense, since the unit
of IO is the page, and once we have the page in the buffer, reading from
anywhere on the page is probably going to "cost" the same.
But then what does it mean to say that a clustered index orders the data
physically in the order of the index? It can't mean that the pages are
contiguous. Otherwise, fragmentation of a clustered index would be
impossible (and its not). Does it simply mean that the pages are in the
order of the index? For example, if the index is on id number, does it mean
that on page 1, if the highest id number is 5, then on page 2, the lowest id
number is greater than 5, but it may not be at the beginning of the page?
If that's so, then Microsoft's documentation and courseware is seriously
misleading in some places. I'd be grateful for any insight into this issue.It means that the pages are in the order of the index, but data with a page
are in no specified order. I don't find the documentation misleading, becaus
e
the smallest I/O is a page, and as you said, once a page is in memory, there
is no performance difference between retrieving the first or the last row in
the page.
Vern
"MAS" wrote:

> I've got a question about how clustered indexes map to the physical order
of
> the records in a database. I've always been under the impression that a
> clustered index orders the records physically in the order of the clustere
d
> key. As an MCT, I've taught this idea many times.
> However, on a whim I just did a page dump of a table, and noticed that the
> records were not in the order of the clustered key (I used the authors tab
le
> in the pubs database). An an aside, a "select *" does display them in
> clustered key order; Also the column order in a "select *" does not match
> the column order in the page dump.
> From a practical point of view, I suppose this makes sense, since the unit
> of IO is the page, and once we have the page in the buffer, reading from
> anywhere on the page is probably going to "cost" the same.
> But then what does it mean to say that a clustered index orders the data
> physically in the order of the index? It can't mean that the pages are
> contiguous. Otherwise, fragmentation of a clustered index would be
> impossible (and its not). Does it simply mean that the pages are in the
> order of the index? For example, if the index is on id number, does it me
an
> that on page 1, if the highest id number is 5, then on page 2, the lowest
id
> number is greater than 5, but it may not be at the beginning of the page?
> If that's so, then Microsoft's documentation and courseware is seriously
> misleading in some places. I'd be grateful for any insight into this issu
e.
>
>|||MAS
There was one of the longest threads in the history of this newsgroup on
this very topic, last w.
Please see the thread entitled "insert into tbl1 select * from tbl2order by
field1 doesnt work!"
It was initially posted on May 23, by someone called CoolTech77. However,
once he posting the original question, he never came back. But someone else
jumped in and swore that because all the docs said 'physical order', it must
be true, in spite of lots of evidence to the contrary.
If you are SQL Server MCT, teaching SQL Server classes, I strongly recommend
you read my book, Inside SQL Server 2000 (2005 is coming soon) so you can
make sure you have the details correct. I actually use the authors table as
an example when talking about page structures.
Also, take a look at this blog entry by David Portas (who was one of the
people on the thread last w) who tried to clear things up.
http://blogs.conchango.com/davidpor...05/30/4007.aspx
But, as an MCT, it would really be worthwhile to also read the whole thread.
HTH
Kalen Delaney, SQL Server MVP
"MAS" <mark_stricker@.hotmail.com> wrote in message
news:%23u6SVzlhGHA.1272@.TK2MSFTNGP03.phx.gbl...
> I've got a question about how clustered indexes map to the physical order
> of the records in a database. I've always been under the impression that
> a clustered index orders the records physically in the order of the
> clustered key. As an MCT, I've taught this idea many times.
> However, on a whim I just did a page dump of a table, and noticed that the
> records were not in the order of the clustered key (I used the authors
> table in the pubs database). An an aside, a "select *" does display them
> in clustered key order; Also the column order in a "select *" does not
> match the column order in the page dump.
> From a practical point of view, I suppose this makes sense, since the unit
> of IO is the page, and once we have the page in the buffer, reading from
> anywhere on the page is probably going to "cost" the same.
> But then what does it mean to say that a clustered index orders the data
> physically in the order of the index? It can't mean that the pages are
> contiguous. Otherwise, fragmentation of a clustered index would be
> impossible (and its not). Does it simply mean that the pages are in the
> order of the index? For example, if the index is on id number, does it
> mean that on page 1, if the highest id number is 5, then on page 2, the
> lowest id number is greater than 5, but it may not be at the beginning of
> the page?
> If that's so, then Microsoft's documentation and courseware is seriously
> misleading in some places. I'd be grateful for any insight into this
> issue.
>|||Thanks,
I'll check those resources out.
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:%23zVzeBmhGHA.1856@.TK2MSFTNGP03.phx.gbl...
> MAS
> There was one of the longest threads in the history of this newsgroup on
> this very topic, last w.
> Please see the thread entitled "insert into tbl1 select * from tbl2order
> by field1 doesnt work!"
> It was initially posted on May 23, by someone called CoolTech77. However,
> once he posting the original question, he never came back. But someone
> else jumped in and swore that because all the docs said 'physical order',
> it must be true, in spite of lots of evidence to the contrary.
> If you are SQL Server MCT, teaching SQL Server classes, I strongly
> recommend you read my book, Inside SQL Server 2000 (2005 is coming soon)
> so you can make sure you have the details correct. I actually use the
> authors table as an example when talking about page structures.
> Also, take a look at this blog entry by David Portas (who was one of the
> people on the thread last w) who tried to clear things up.
> http://blogs.conchango.com/davidpor...05/30/4007.aspx
> But, as an MCT, it would really be worthwhile to also read the whole
> thread.
>
> --
> HTH
> Kalen Delaney, SQL Server MVP
>
> "MAS" <mark_stricker@.hotmail.com> wrote in message
> news:%23u6SVzlhGHA.1272@.TK2MSFTNGP03.phx.gbl...
>|||What I found misleading was the Microsoft Courseware for both SQL 7.0 and
SQL 2000 where they discuss indexing. They have a multimedia presentations
that actually shows the rows in sorted order on each page.
"Vern Rabe" <VernRabe@.discussions.microsoft.com> wrote in message
news:163DEF0C-FE6C-41FC-B06E-6A5BCA1A0331@.microsoft.com...
> It means that the pages are in the order of the index, but data with a
> page
> are in no specified order. I don't find the documentation misleading,
> because
> the smallest I/O is a page, and as you said, once a page is in memory,
> there
> is no performance difference between retrieving the first or the last row
> in
> the page.
> Vern
> "MAS" wrote:
>|||Yes, the courseware has many inaccuracies. It is up to the trainer to know
the product well enough to recognize when the courseware is incomplete and
to pursue deeper. (I know that's just my opinion, and not everyone shares
it. Some people think all a trainer has to be able to do is read the
slides.)
I provide tools in Inside SQL Server that you can use to see for yourself
how the data is physically organized.
HTH
Kalen Delaney, SQL Server MVP
"MAS" <mark_stricker@.hotmail.com> wrote in message
news:%23M11jCLiGHA.4276@.TK2MSFTNGP03.phx.gbl...
> What I found misleading was the Microsoft Courseware for both SQL 7.0 and
> SQL 2000 where they discuss indexing. They have a multimedia
> presentations that actually shows the rows in sorted order on each page.
>
> "Vern Rabe" <VernRabe@.discussions.microsoft.com> wrote in message
> news:163DEF0C-FE6C-41FC-B06E-6A5BCA1A0331@.microsoft.com...
>|||Now, that I've looked at it, I remember reading that thread. I'm not under
the misunderstanding that the data is ordered physically (what a long
thread!). I understand file fragmentation both exterior and interior. I
was just surprised on looking at a page dump that even WITHIN a page the
data didn't seem to be in physical order.
On seeing that, the question in my mind was, OK, what could be meant by
logical ordering, if, even within a page, a clustered index doesn't order
the rows? It seems to me that within a page, the rows aren't even logically
ordered. The IAM pages can define the logical "order" of the data pages, I
suppose. So I'm left with the surmise that logically ordering doesn't mean
logical ordering of the rows within a page, but the logical ordering of rows
between pages.
For example, we have a clustered index on letter. On page 1, we have ABCD
but they are in the order CADB. On page 2 (which may be anywhere in the
file physically) we have FGHI, but they are in the order GFIH. If the pages
are full and I insert E, the page 2 splits and has EGF on page 2 and I on
page 3.
Is this a correct understanding? (And Kalen, I will make an attempt to read
your book; I've been meaning to for years -- but, I am an MCT and I also
have a full time job as a SQL Engineer AND four kids with soccer and
baseball games to watch -- so I don't have a lot of time. :))
Thanks again.
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:%23zVzeBmhGHA.1856@.TK2MSFTNGP03.phx.gbl...
> MAS
> There was one of the longest threads in the history of this newsgroup on
> this very topic, last w.
> Please see the thread entitled "insert into tbl1 select * from tbl2order
> by field1 doesnt work!"
> It was initially posted on May 23, by someone called CoolTech77. However,
> once he posting the original question, he never came back. But someone
> else jumped in and swore that because all the docs said 'physical order',
> it must be true, in spite of lots of evidence to the contrary.
> If you are SQL Server MCT, teaching SQL Server classes, I strongly
> recommend you read my book, Inside SQL Server 2000 (2005 is coming soon)
> so you can make sure you have the details correct. I actually use the
> authors table as an example when talking about page structures.
> Also, take a look at this blog entry by David Portas (who was one of the
> people on the thread last w) who tried to clear things up.
> http://blogs.conchango.com/davidpor...05/30/4007.aspx
> But, as an MCT, it would really be worthwhile to also read the whole
> thread.
>
> --
> HTH
> Kalen Delaney, SQL Server MVP
>
> "MAS" <mark_stricker@.hotmail.com> wrote in message
> news:%23u6SVzlhGHA.1272@.TK2MSFTNGP03.phx.gbl...
>|||Good point. The SQL courses 2733 and 2734 are particularly egregious
examples of inaccurate courseware.
And BTW, even the trainers that just read slides wouldn't admit it -- it's
usually the people hiring the trainers that think all there is to the job is
reading slides.
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:enlhvYLiGHA.1000@.TK2MSFTNGP04.phx.gbl...
> Yes, the courseware has many inaccuracies. It is up to the trainer to know
> the product well enough to recognize when the courseware is incomplete and
> to pursue deeper. (I know that's just my opinion, and not everyone shares
> it. Some people think all a trainer has to be able to do is read the
> slides.)
> I provide tools in Inside SQL Server that you can use to see for yourself
> how the data is physically organized.
> --
> HTH
> Kalen Delaney, SQL Server MVP
>
> "MAS" <mark_stricker@.hotmail.com> wrote in message
> news:%23M11jCLiGHA.4276@.TK2MSFTNGP03.phx.gbl...
>|||It's all still logical ordering. Page to page ordering is with pointers. On
the pages themselves, there is a set of bytes at the bottom of the page that
gives the offsets of the rows in logical order. I describe these row-offset
bytes in detail in my book and in my course.
It's all for performance reasons. If the rows were stored physically in
order on pages, inserts would mean shuffling rows around on the pages. This
way, a new row can go wherever there is room, and only a few bytes at the
end of the page need to be adjusted.

> your book; I've been meaning to for years -- but, I am an MCT and I also
> have a full time job as a SQL Engineer AND four kids with soccer and
> baseball games to watch -- so I don't have a lot of time. :))
Excuses, excuses ...
I've got 4 kids with classroom meetings, choir concerts and drama
productions to watch, a day job and a night job as assistant instructor at a
martial arts dojo, and MVP responsibilities to answer questions on the
newsgroups, so if I can WRITE a book, you can certainly READ it. ;-)
(Actually, this is just MY excuse for why Inside SQL Server 2005 isn't done
yet. :-) )
HTH
Kalen Delaney, SQL Server MVP
"MAS" <mark_stricker@.hotmail.com> wrote in message
news:Oy0P0aMiGHA.3296@.TK2MSFTNGP05.phx.gbl...
> Now, that I've looked at it, I remember reading that thread. I'm not
> under the misunderstanding that the data is ordered physically (what a
> long thread!). I understand file fragmentation both exterior and
> interior. I was just surprised on looking at a page dump that even WITHIN
> a page the data didn't seem to be in physical order.
> On seeing that, the question in my mind was, OK, what could be meant by
> logical ordering, if, even within a page, a clustered index doesn't order
> the rows? It seems to me that within a page, the rows aren't even
> logically ordered. The IAM pages can define the logical "order" of the
> data pages, I suppose. So I'm left with the surmise that logically
> ordering doesn't mean logical ordering of the rows within a page, but the
> logical ordering of rows between pages.
> For example, we have a clustered index on letter. On page 1, we have ABCD
> but they are in the order CADB. On page 2 (which may be anywhere in the
> file physically) we have FGHI, but they are in the order GFIH. If the
> pages are full and I insert E, the page 2 splits and has EGF on page 2 and
> I on page 3.
> Is this a correct understanding? (And Kalen, I will make an attempt to
> read your book; I've been meaning to for years -- but, I am an MCT and I
> also have a full time job as a SQL Engineer AND four kids with soccer and
> baseball games to watch -- so I don't have a lot of time. :))
> Thanks again.
>
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:%23zVzeBmhGHA.1856@.TK2MSFTNGP03.phx.gbl...
>|||I completely understand about the training centers. It's a very rare one
that really works hard to make sure the best possible instructor is in the
room for each course. I know of a few like that, but not many.
I assume you are aware that there is a private MCT forum (with a group
dedicated to SQL Server), where you can discuss the inaccuracies in the
courseware with other SQL MCTs. Several of the most active MVPs here are
also MCTs and you can get lots of very specific MOC-related info there.
HTH
Kalen Delaney, SQL Server MVP
"MAS" <mark_stricker@.hotmail.com> wrote in message
news:e0xF44MiGHA.4892@.TK2MSFTNGP02.phx.gbl...
> Good point. The SQL courses 2733 and 2734 are particularly egregious
> examples of inaccurate courseware.
> And BTW, even the trainers that just read slides wouldn't admit it -- it's
> usually the people hiring the trainers that think all there is to the job
> is reading slides.
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:enlhvYLiGHA.1000@.TK2MSFTNGP04.phx.gbl...
>

Clustered Index or ORDER BY?

Hello,
I'll ask my question up front followed by all the details in case they are
necessary.
In reviewing SQL Books On Line it indicates a clustered index is suitable
for ordering data, instead of using ORDER BY in the query. I have seen some
cases where a clustered index does not work this way, and the only solution
was to recreate the clustered index, or use ORDER BY (or alternatively set
MAX_DOP=1). Some industry forum discussions indicate that ORDER BY is the
only way to guarantee the order of the rows, which supports by own
experiences in the matter. I'd like to know what is correct, and why.
My problem table, TableA, holds temporary data. The older data is deleted
from the back of the table and newer data is inserted in at the front. The
table uses a unique index on one column and a unique clustered index on
another column. Both columns are Ints. I have seen some cases where queries
don't return the rows in the correct order, and it is very important they be
in the correct order.
TableA
/* Col1 has a nonclustered unique index. Col1 increments like an identity,
but it's not one. */
Col1 int
Col2 varchar(100) -- user data
/*Col3 has a clustered index (not defined as unique) */
Col3 int identity(1,1)
At some point in time, only queries like this work correctly:
Select * from TableA
--or
Select * from TableA ORDER BY Col1
--or
Select * from TableA ORDER BY Col3
But queries with filters such as LIKE don't return the rows in the correct
order.
Select * from TableA Where Col3 LIKE 'SOMEVALUE%'
This behavior was found on a mutli-processor server running SQL 2000 SP4 and
was recreated on a copy of the database on another server, also with multiple
processors and SQL 2000 SP4. I've found that specifying OPTION (MAXDOP 1)
fixes the problem when using LIKE. (I have not tested the same situation on
other platforms such as single-processor servers or previous SP's.)
Again I'm interested in knowing if it is correct/valid to use clustered
indexes as a way to order query results, or if it is truly necessary to
specify ORDER BY to get the results in the correct order, and the reasons for
it. Thanks in advance!Correction: the query on TableA using LIKE should read this:
Select * from TableA Where Col2 LIKE 'SOMEVALUE%'
"Mike K" wrote:
> Hello,
> I'll ask my question up front followed by all the details in case they are
> necessary.
> In reviewing SQL Books On Line it indicates a clustered index is suitable
> for ordering data, instead of using ORDER BY in the query. I have seen some
> cases where a clustered index does not work this way, and the only solution
> was to recreate the clustered index, or use ORDER BY (or alternatively set
> MAX_DOP=1). Some industry forum discussions indicate that ORDER BY is the
> only way to guarantee the order of the rows, which supports by own
> experiences in the matter. I'd like to know what is correct, and why.
> My problem table, TableA, holds temporary data. The older data is deleted
> from the back of the table and newer data is inserted in at the front. The
> table uses a unique index on one column and a unique clustered index on
> another column. Both columns are Ints. I have seen some cases where queries
> don't return the rows in the correct order, and it is very important they be
> in the correct order.
> TableA
> /* Col1 has a nonclustered unique index. Col1 increments like an identity,
> but it's not one. */
> Col1 int
> Col2 varchar(100) -- user data
> /*Col3 has a clustered index (not defined as unique) */
> Col3 int identity(1,1)
> At some point in time, only queries like this work correctly:
> Select * from TableA
> --or
> Select * from TableA ORDER BY Col1
> --or
> Select * from TableA ORDER BY Col3
> But queries with filters such as LIKE don't return the rows in the correct
> order.
> Select * from TableA Where Col3 LIKE 'SOMEVALUE%'
> This behavior was found on a mutli-processor server running SQL 2000 SP4 and
> was recreated on a copy of the database on another server, also with multiple
> processors and SQL 2000 SP4. I've found that specifying OPTION (MAXDOP 1)
> fixes the problem when using LIKE. (I have not tested the same situation on
> other platforms such as single-processor servers or previous SP's.)
> Again I'm interested in knowing if it is correct/valid to use clustered
> indexes as a way to order query results, or if it is truly necessary to
> specify ORDER BY to get the results in the correct order, and the reasons for
> it. Thanks in advance!|||Ive read on the web several times that ORDER BY is the ONLY way to gaurantee
the resulst coming back a certain way. Ive NEVER read anything suggesting
that a clustered index is used for this.
--
TIA,
ChrisR
"Mike K" wrote:
> Correction: the query on TableA using LIKE should read this:
> Select * from TableA Where Col2 LIKE 'SOMEVALUE%'
> "Mike K" wrote:
> > Hello,
> >
> > I'll ask my question up front followed by all the details in case they are
> > necessary.
> >
> > In reviewing SQL Books On Line it indicates a clustered index is suitable
> > for ordering data, instead of using ORDER BY in the query. I have seen some
> > cases where a clustered index does not work this way, and the only solution
> > was to recreate the clustered index, or use ORDER BY (or alternatively set
> > MAX_DOP=1). Some industry forum discussions indicate that ORDER BY is the
> > only way to guarantee the order of the rows, which supports by own
> > experiences in the matter. I'd like to know what is correct, and why.
> >
> > My problem table, TableA, holds temporary data. The older data is deleted
> > from the back of the table and newer data is inserted in at the front. The
> > table uses a unique index on one column and a unique clustered index on
> > another column. Both columns are Ints. I have seen some cases where queries
> > don't return the rows in the correct order, and it is very important they be
> > in the correct order.
> >
> > TableA
> > /* Col1 has a nonclustered unique index. Col1 increments like an identity,
> > but it's not one. */
> > Col1 int
> > Col2 varchar(100) -- user data
> > /*Col3 has a clustered index (not defined as unique) */
> > Col3 int identity(1,1)
> >
> > At some point in time, only queries like this work correctly:
> > Select * from TableA
> > --or
> > Select * from TableA ORDER BY Col1
> > --or
> > Select * from TableA ORDER BY Col3
> >
> > But queries with filters such as LIKE don't return the rows in the correct
> > order.
> > Select * from TableA Where Col3 LIKE 'SOMEVALUE%'
> >
> > This behavior was found on a mutli-processor server running SQL 2000 SP4 and
> > was recreated on a copy of the database on another server, also with multiple
> > processors and SQL 2000 SP4. I've found that specifying OPTION (MAXDOP 1)
> > fixes the problem when using LIKE. (I have not tested the same situation on
> > other platforms such as single-processor servers or previous SP's.)
> >
> > Again I'm interested in knowing if it is correct/valid to use clustered
> > indexes as a way to order query results, or if it is truly necessary to
> > specify ORDER BY to get the results in the correct order, and the reasons for
> > it. Thanks in advance!|||Mike K wrote:
> Hello,
> I'll ask my question up front followed by all the details in case
> they are necessary.
> In reviewing SQL Books On Line it indicates a clustered index is
> suitable for ordering data, instead of using ORDER BY in the query. I
> have seen some cases where a clustered index does not work this way,
> and the only solution was to recreate the clustered index, or use
> ORDER BY (or alternatively set MAX_DOP=1). Some industry forum
> discussions indicate that ORDER BY is the only way to guarantee the
> order of the rows, which supports by own experiences in the matter.
> I'd like to know what is correct, and why.
> My problem table, TableA, holds temporary data. The older data is
> deleted from the back of the table and newer data is inserted in at
> the front. The table uses a unique index on one column and a unique
> clustered index on another column. Both columns are Ints. I have seen
> some cases where queries don't return the rows in the correct order,
> and it is very important they be in the correct order.
> TableA
> /* Col1 has a nonclustered unique index. Col1 increments like an
> identity, but it's not one. */
> Col1 int
> Col2 varchar(100) -- user data
> /*Col3 has a clustered index (not defined as unique) */
> Col3 int identity(1,1)
> At some point in time, only queries like this work correctly:
> Select * from TableA
> --or
> Select * from TableA ORDER BY Col1
> --or
> Select * from TableA ORDER BY Col3
> But queries with filters such as LIKE don't return the rows in the
> correct order.
> Select * from TableA Where Col3 LIKE 'SOMEVALUE%'
> This behavior was found on a mutli-processor server running SQL 2000
> SP4 and was recreated on a copy of the database on another server,
> also with multiple processors and SQL 2000 SP4. I've found that
> specifying OPTION (MAXDOP 1) fixes the problem when using LIKE. (I
> have not tested the same situation on other platforms such as
> single-processor servers or previous SP's.)
> Again I'm interested in knowing if it is correct/valid to use
> clustered indexes as a way to order query results, or if it is truly
> necessary to specify ORDER BY to get the results in the correct
> order, and the reasons for it. Thanks in advance!
You must use an ORDER BY to guarantee order.
--
David Gugick
Quest Software
www.imceda.com
www.quest.com|||ORDER BY is indeed the only way to guarantee that results are coming back in
a specific order. If the ORDER BY is on the column(s) that make up the
clustered index, SQL Server will likely use that clustered index to look up
the rows, and the order in which to return them, although it could use a
different index if an appropriate one exists. For example if you have a
table with a large number of columns, and you only need a column that has a
non-clustered index plus the column(s) from the clustered index, on which
you order, it is possible that SQL Server decides it is more efficient to
use the narrower non clustered index (which includes the columns of the
clustered index by definition) and sort it, rather than using the wider
clustered index.
IIRC, until SQL Server version 6.0 or 6.5, selecting from a table that had a
clustered index would always return the rows in the order of a clustered
index, unless you specified an ORDER BY clause with a different order. The
Query Optimizer in later versions is more advanced and efficient, and
doesn't do the work of accessing the rows in the order of the clustered
index unless you specifically say so.
--
Jacco Schalkwijk
SQL Server MVP
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:90CF9131-3F8A-44F0-9CCF-DCA1D0A2D2E8@.microsoft.com...
> Ive read on the web several times that ORDER BY is the ONLY way to
> gaurantee
> the resulst coming back a certain way. Ive NEVER read anything suggesting
> that a clustered index is used for this.
> --
> TIA,
> ChrisR
>
> "Mike K" wrote:
>> Correction: the query on TableA using LIKE should read this:
>> Select * from TableA Where Col2 LIKE 'SOMEVALUE%'
>> "Mike K" wrote:
>> > Hello,
>> >
>> > I'll ask my question up front followed by all the details in case they
>> > are
>> > necessary.
>> >
>> > In reviewing SQL Books On Line it indicates a clustered index is
>> > suitable
>> > for ordering data, instead of using ORDER BY in the query. I have seen
>> > some
>> > cases where a clustered index does not work this way, and the only
>> > solution
>> > was to recreate the clustered index, or use ORDER BY (or alternatively
>> > set
>> > MAX_DOP=1). Some industry forum discussions indicate that ORDER BY is
>> > the
>> > only way to guarantee the order of the rows, which supports by own
>> > experiences in the matter. I'd like to know what is correct, and why.
>> >
>> > My problem table, TableA, holds temporary data. The older data is
>> > deleted
>> > from the back of the table and newer data is inserted in at the front.
>> > The
>> > table uses a unique index on one column and a unique clustered index on
>> > another column. Both columns are Ints. I have seen some cases where
>> > queries
>> > don't return the rows in the correct order, and it is very important
>> > they be
>> > in the correct order.
>> >
>> > TableA
>> > /* Col1 has a nonclustered unique index. Col1 increments like an
>> > identity,
>> > but it's not one. */
>> > Col1 int
>> > Col2 varchar(100) -- user data
>> > /*Col3 has a clustered index (not defined as unique) */
>> > Col3 int identity(1,1)
>> >
>> > At some point in time, only queries like this work correctly:
>> > Select * from TableA
>> > --or
>> > Select * from TableA ORDER BY Col1
>> > --or
>> > Select * from TableA ORDER BY Col3
>> >
>> > But queries with filters such as LIKE don't return the rows in the
>> > correct
>> > order.
>> > Select * from TableA Where Col3 LIKE 'SOMEVALUE%'
>> >
>> > This behavior was found on a mutli-processor server running SQL 2000
>> > SP4 and
>> > was recreated on a copy of the database on another server, also with
>> > multiple
>> > processors and SQL 2000 SP4. I've found that specifying OPTION (MAXDOP
>> > 1)
>> > fixes the problem when using LIKE. (I have not tested the same
>> > situation on
>> > other platforms such as single-processor servers or previous SP's.)
>> >
>> > Again I'm interested in knowing if it is correct/valid to use clustered
>> > indexes as a way to order query results, or if it is truly necessary to
>> > specify ORDER BY to get the results in the correct order, and the
>> > reasons for
>> > it. Thanks in advance!|||> In reviewing SQL Books On Line it indicates a clustered index is suitable
> for ordering data, instead of using ORDER BY in the query.
Do you happen to remember which topic(s) you looked at? This is inaccurate
information. I believe we have corrected this in SQL Server 2005 Books
Online, but it would be very useful if you could provide the topic that gave
you that idea.
Thanks,
--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
"Mike K" <MikeK@.discussions.microsoft.com> wrote in message
news:92F631A4-F035-4E60-B6D7-D23EEEEB3A21@.microsoft.com...
> Hello,
> I'll ask my question up front followed by all the details in case they are
> necessary.
> In reviewing SQL Books On Line it indicates a clustered index is suitable
> for ordering data, instead of using ORDER BY in the query. I have seen
> some
> cases where a clustered index does not work this way, and the only
> solution
> was to recreate the clustered index, or use ORDER BY (or alternatively set
> MAX_DOP=1). Some industry forum discussions indicate that ORDER BY is the
> only way to guarantee the order of the rows, which supports by own
> experiences in the matter. I'd like to know what is correct, and why.
> My problem table, TableA, holds temporary data. The older data is deleted
> from the back of the table and newer data is inserted in at the front.
> The
> table uses a unique index on one column and a unique clustered index on
> another column. Both columns are Ints. I have seen some cases where
> queries
> don't return the rows in the correct order, and it is very important they
> be
> in the correct order.
> TableA
> /* Col1 has a nonclustered unique index. Col1 increments like an identity,
> but it's not one. */
> Col1 int
> Col2 varchar(100) -- user data
> /*Col3 has a clustered index (not defined as unique) */
> Col3 int identity(1,1)
> At some point in time, only queries like this work correctly:
> Select * from TableA
> --or
> Select * from TableA ORDER BY Col1
> --or
> Select * from TableA ORDER BY Col3
> But queries with filters such as LIKE don't return the rows in the correct
> order.
> Select * from TableA Where Col3 LIKE 'SOMEVALUE%'
> This behavior was found on a mutli-processor server running SQL 2000 SP4
> and
> was recreated on a copy of the database on another server, also with
> multiple
> processors and SQL 2000 SP4. I've found that specifying OPTION (MAXDOP 1)
> fixes the problem when using LIKE. (I have not tested the same situation
> on
> other platforms such as single-processor servers or previous SP's.)
> Again I'm interested in knowing if it is correct/valid to use clustered
> indexes as a way to order query results, or if it is truly necessary to
> specify ORDER BY to get the results in the correct order, and the reasons
> for
> it. Thanks in advance!|||This is a pretty simple question. The ONLY way to guarantee a defined
output is to use an ORDER BY clause. The word "only" in this sentence
cannot be stressed too much!!
In general, when using SQL, you should never rely on particular
implementation details of an RDBMS, because the implementation may
change with every version, fix or service pack. An example of this is
the implied ordering in SQL-Server 6.5 when using a GROUP BY which was
no longer the case in SQL-Server 7.0.
The implementation may even behave differently on different platforms or
different configurations. An example is the predictability of the
NEWID() function on Windows NT versus the 'random' implementation on
Windows 2000/2003.
Gert-Jan
Mike K wrote:
> Hello,
> I'll ask my question up front followed by all the details in case they are
> necessary.
> In reviewing SQL Books On Line it indicates a clustered index is suitable
> for ordering data, instead of using ORDER BY in the query. I have seen some
> cases where a clustered index does not work this way, and the only solution
> was to recreate the clustered index, or use ORDER BY (or alternatively set
> MAX_DOP=1). Some industry forum discussions indicate that ORDER BY is the
> only way to guarantee the order of the rows, which supports by own
> experiences in the matter. I'd like to know what is correct, and why.
> My problem table, TableA, holds temporary data. The older data is deleted
> from the back of the table and newer data is inserted in at the front. The
> table uses a unique index on one column and a unique clustered index on
> another column. Both columns are Ints. I have seen some cases where queries
> don't return the rows in the correct order, and it is very important they be
> in the correct order.
> TableA
> /* Col1 has a nonclustered unique index. Col1 increments like an identity,
> but it's not one. */
> Col1 int
> Col2 varchar(100) -- user data
> /*Col3 has a clustered index (not defined as unique) */
> Col3 int identity(1,1)
> At some point in time, only queries like this work correctly:
> Select * from TableA
> --or
> Select * from TableA ORDER BY Col1
> --or
> Select * from TableA ORDER BY Col3
> But queries with filters such as LIKE don't return the rows in the correct
> order.
> Select * from TableA Where Col3 LIKE 'SOMEVALUE%'
> This behavior was found on a mutli-processor server running SQL 2000 SP4 and
> was recreated on a copy of the database on another server, also with multiple
> processors and SQL 2000 SP4. I've found that specifying OPTION (MAXDOP 1)
> fixes the problem when using LIKE. (I have not tested the same situation on
> other platforms such as single-processor servers or previous SP's.)
> Again I'm interested in knowing if it is correct/valid to use clustered
> indexes as a way to order query results, or if it is truly necessary to
> specify ORDER BY to get the results in the correct order, and the reasons for
> it. Thanks in advance!|||I found it under the topic "clustered indexes, overview". However I beleive I
may have misread or misinterpreted what it said.
I originally interpreted it to mean a clustered index can replace the ORDER
BY. But in re-reading it, I'm interpreting it as meaning queries using ORDER
BY with a clustered index column would run faster than if the clustered index
hadn't been there. This interpretation agrees with everyone's responses.
"Gail Erickson [MS]" wrote:
> > In reviewing SQL Books On Line it indicates a clustered index is suitable
> > for ordering data, instead of using ORDER BY in the query.
> Do you happen to remember which topic(s) you looked at? This is inaccurate
> information. I believe we have corrected this in SQL Server 2005 Books
> Online, but it would be very useful if you could provide the topic that gave
> you that idea.
> Thanks,
> --
> Gail Erickson [MS]
> SQL Server Documentation Team
> This posting is provided "AS IS" with no warranties, and confers no rights
> "Mike K" <MikeK@.discussions.microsoft.com> wrote in message
> news:92F631A4-F035-4E60-B6D7-D23EEEEB3A21@.microsoft.com...
> > Hello,
> >
> > I'll ask my question up front followed by all the details in case they are
> > necessary.
> >
> > In reviewing SQL Books On Line it indicates a clustered index is suitable
> > for ordering data, instead of using ORDER BY in the query. I have seen
> > some
> > cases where a clustered index does not work this way, and the only
> > solution
> > was to recreate the clustered index, or use ORDER BY (or alternatively set
> > MAX_DOP=1). Some industry forum discussions indicate that ORDER BY is the
> > only way to guarantee the order of the rows, which supports by own
> > experiences in the matter. I'd like to know what is correct, and why.
> >
> > My problem table, TableA, holds temporary data. The older data is deleted
> > from the back of the table and newer data is inserted in at the front.
> > The
> > table uses a unique index on one column and a unique clustered index on
> > another column. Both columns are Ints. I have seen some cases where
> > queries
> > don't return the rows in the correct order, and it is very important they
> > be
> > in the correct order.
> >
> > TableA
> > /* Col1 has a nonclustered unique index. Col1 increments like an identity,
> > but it's not one. */
> > Col1 int
> > Col2 varchar(100) -- user data
> > /*Col3 has a clustered index (not defined as unique) */
> > Col3 int identity(1,1)
> >
> > At some point in time, only queries like this work correctly:
> > Select * from TableA
> > --or
> > Select * from TableA ORDER BY Col1
> > --or
> > Select * from TableA ORDER BY Col3
> >
> > But queries with filters such as LIKE don't return the rows in the correct
> > order.
> > Select * from TableA Where Col3 LIKE 'SOMEVALUE%'
> >
> > This behavior was found on a mutli-processor server running SQL 2000 SP4
> > and
> > was recreated on a copy of the database on another server, also with
> > multiple
> > processors and SQL 2000 SP4. I've found that specifying OPTION (MAXDOP 1)
> > fixes the problem when using LIKE. (I have not tested the same situation
> > on
> > other platforms such as single-processor servers or previous SP's.)
> >
> > Again I'm interested in knowing if it is correct/valid to use clustered
> > indexes as a way to order query results, or if it is truly necessary to
> > specify ORDER BY to get the results in the correct order, and the reasons
> > for
> > it. Thanks in advance!
>
>