collation error for patindex (sql 2000)

Hi, what do I do with this error please:
Server: Msg 446, Level 16, State 9, Line 3
Cannot resolve collation conflict for patindex operation

Here is the script in question.

use mosaikDB737
exec sp_MSforeachDB
'
use [?]
insert into mosaikDB737.dbo.SearchOutput2 select sk.loginname as searchedTxt, object_name(id) AS ProcName ,Len(SubString(o.text,1, PatIndex(''%'' + ltrim(rtrim(sk.loginname))+ ''%'', o.text)))-Len(Replace(SubString(o.text,1, PatIndex
(''%'' + ltrim(rtrim(sk.loginname)) + ''%'', o.text)),char(13),''''))+1 AS Line,
PatIndex(''%'' + ltrim(rtrim(sk.loginname)) + ''%'', o.text) AS Position, ''[?]'' as dbName
from syscomments as o inner join mosaikDB737.dbo.loginListInput as sk on o.text like ''%'' + ltrim(rtrim(sk.loginname)) + ''%''
ORDER BY searchedTxt,ProcName, Line, position'
select * from mosaikDB737.dbo.SearchOutput2

Thanks a lot

Check collation of loginname and text columns. Depending on your installation / configuration you may have to modify one or the other to the corresponding collation using the COLLATE clause. See the COLLATE topic in Books Online for more details on collations, how they are used in string comparisons etc.|||they re different in some databases man. Can I cast loginname to the collation of o.text on the fly dynamically in my script?|||You can't use COLLATE clause dynamically. So you will have to cast both columns to a common collation and then do the comparison. Of course, this means that you will have to pick a collation that will work with your data otherwise you will get incorrect results.|||

that s the pb man. as u can see. using "?" my script goes through all the databases. So if one of the databases has a different collation for the column syscomments.text than the one i will choose then all my script might fail.

|||As I suggested, you change each column that you are comparing to a common collation. This may or may not work depending on the data.|||That was perfect|||

Umachandar Jayachandran - MS wrote:

Check collation of loginname and text columns. Depending on your installation / configuration you may have to modify one or the other to the corresponding collation using the COLLATE clause.

P.S: First I want to let you know that the collation issue has been solved thanks to your help.

I read Collate in BOL but i m still not sure how that works exactly for my script.

for loginname column I have control over it since it s in a database that I create my self. But as you can see text column represents the text for all the SPs of all the databases in the instance.

After checking we found that the text column is 29 times of collation Latin1_General_BIN and 13 times of collation SQL_Latin1_General_CP1_CI_AS.

So my question pls is this:

as you can see that in my script I have the columns sk.LoginName and o.text many times. If I choose to cast both columns to the collation SQL_Latin1_General_CP1_CI_AS. Will I have to put your suggestion COLLATE SQL_Latin1_General_CP1_CI_AS after each occurence of those 2 columns in my script or just in one occurence.

Tell me please if this is correct. I put your suggestion in all the loginname and text occurences in the script (I mean whenever there is loginname or o.text in the script I put COLLATE SQL_Latin1_General_CP1_CI_AS ) as in teh following:

use mosaikDB737
exec sp_MSforeachdb
'
use [?]
insert into mosaikDB737.dbo.SearchOutput1 select sk.LoginName COLLATE

SQL_Latin1_General_CP1_CI_AS as searchedTxt , object_name(id) AS ProcName ,Len(SubString

(o.text,1, PatIndex(''%'' + ltrim(rtrim(sk.LoginName COLLATE

SQL_Latin1_General_CP1_CI_AS))+ ''%'', o.text)))-Len(Replace(SubString(o.text,1, PatIndex
(''%'' + ltrim(rtrim(sk.LoginName COLLATE SQL_Latin1_General_CP1_CI_AS)) + ''%'', o.text

COLLATE SQL_Latin1_General_CP1_CI_AS)),char(13),''''))+1 AS Line,
PatIndex(''%'' + ltrim(rtrim(sk.LoginName COLLATE SQL_Latin1_General_CP1_CI_AS)) + ''%'',

o.text COLLATE SQL_Latin1_General_CP1_CI_AS) AS Position, ''[?]'' as dbName
from syscomments as o inner join mosaikDB737.dbo.LoginListInput as sk on o.text like

''%'' + ltrim(rtrim(sk.LoginName COLLATE SQL_Latin1_General_CP1_CI_AS)) + ''%''
ORDER BY searchedTxt,ProcName, Line, Position'
select * from mosaikDB737.dbo.SearchOutput1 order by dbName, ProcName

I just chose to cast every thing to the collation SQL_Latin1_General_CP1_CI_AS. I hope that s Ok.

Muchas Gracias

COLLATE problem

Why do i get the error below:
Server: Msg 446, Level 16, State 9, Line 1
Cannot resolve collation conflict for equal to operation.
What should i do?
INSERT INTO tkl_proposals(
proposal_id, username, oc_group, oc_place, oc_initial, owner_name,
front_page_tr, front_page_en, notes, owner_company_id, status,
proposal_date, transfer_date, from_server)
SELECT
proposal_id, username, oc_group, oc_place, oc_initial, owner_name,
front_page_tr, front_page_en, notes, owner_company_id, status,
proposal_date, getdate(), 'KIRMIZI'
FROM MD_F.MidasLocal.dbo.tkl_proposals b
WHERE
(NOT EXISTS (SELECT NULL FROM tkl_proposals a WHERE
b.proposal_id=a.proposal_id AND
b.oc_initial=a.oc_initial AND
b.oc_place=a.oc_place)
)
AND b.oc_initial='MD'
AND b.oc_place='F'
AND b.proposal_date >= '1-1-2005'
Zulu
You join two tables on varchar columns which have a different collation.
Table1.LastName=Table2.surname COLLATE HEBREW_CI_AS (Change it for your
needs)
That means Table2 has not the same collation as Table1 and by using above
hints I define to use this praticular collation.
"zulu" <zkendir@.simternet.com> wrote in message
news:O2ZXLyeCFHA.2876@.TK2MSFTNGP12.phx.gbl...
> Why do i get the error below:
> Server: Msg 446, Level 16, State 9, Line 1
> Cannot resolve collation conflict for equal to operation.
> What should i do?
>
>
> INSERT INTO tkl_proposals(
> proposal_id, username, oc_group, oc_place, oc_initial, owner_name,
> front_page_tr, front_page_en, notes, owner_company_id, status,
> proposal_date, transfer_date, from_server)
> SELECT
> proposal_id, username, oc_group, oc_place, oc_initial, owner_name,
> front_page_tr, front_page_en, notes, owner_company_id, status,
> proposal_date, getdate(), 'KIRMIZI'
> FROM MD_F.MidasLocal.dbo.tkl_proposals b
> WHERE
> (NOT EXISTS (SELECT NULL FROM tkl_proposals a WHERE
> b.proposal_id=a.proposal_id AND
> b.oc_initial=a.oc_initial AND
> b.oc_place=a.oc_place)
> )
> AND b.oc_initial='MD'
> AND b.oc_place='F'
> AND b.proposal_date >= '1-1-2005'
>
|||> What should i do?
It's a good practice to provide DDL (CREATE TABLE statements) so that we
can better help you.
This error may be due do different collations on the joined columns. You
can explicitly specify the desired collation using a COLLATE clause in your
query. See the Bools Online for more information.
Hope this helps.
Dan Guzman
SQL Server MVP
"zulu" <zkendir@.simternet.com> wrote in message
news:O2ZXLyeCFHA.2876@.TK2MSFTNGP12.phx.gbl...
> Why do i get the error below:
> Server: Msg 446, Level 16, State 9, Line 1
> Cannot resolve collation conflict for equal to operation.
> What should i do?
>
>
> INSERT INTO tkl_proposals(
> proposal_id, username, oc_group, oc_place, oc_initial, owner_name,
> front_page_tr, front_page_en, notes, owner_company_id, status,
> proposal_date, transfer_date, from_server)
> SELECT
> proposal_id, username, oc_group, oc_place, oc_initial, owner_name,
> front_page_tr, front_page_en, notes, owner_company_id, status,
> proposal_date, getdate(), 'KIRMIZI'
> FROM MD_F.MidasLocal.dbo.tkl_proposals b
> WHERE
> (NOT EXISTS (SELECT NULL FROM tkl_proposals a WHERE
> b.proposal_id=a.proposal_id AND
> b.oc_initial=a.oc_initial AND
> b.oc_place=a.oc_place)
> )
> AND b.oc_initial='MD'
> AND b.oc_place='F'
> AND b.proposal_date >= '1-1-2005'
>
|||ok, got it, thank you
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:O$CfLGfCFHA.4052@.TK2MSFTNGP15.phx.gbl...
> Zulu
> You join two tables on varchar columns which have a different collation.
> Table1.LastName=Table2.surname COLLATE HEBREW_CI_AS (Change it for your
> needs)
> That means Table2 has not the same collation as Table1 and by using above
> hints I define to use this praticular collation.
>
> "zulu" <zkendir@.simternet.com> wrote in message
> news:O2ZXLyeCFHA.2876@.TK2MSFTNGP12.phx.gbl...
>

COLLATE problem

Why do i get the error below:
Server: Msg 446, Level 16, State 9, Line 1
Cannot resolve collation conflict for equal to operation.
What should i do?
INSERT INTO tkl_proposals(
proposal_id, username, oc_group, oc_place, oc_initial, owner_name,
front_page_tr, front_page_en, notes, owner_company_id, status,
proposal_date, transfer_date, from_server)
SELECT
proposal_id, username, oc_group, oc_place, oc_initial, owner_name,
front_page_tr, front_page_en, notes, owner_company_id, status,
proposal_date, getdate(), 'KIRMIZI'
FROM MD_F.MidasLocal.dbo.tkl_proposals b
WHERE
(NOT EXISTS (SELECT NULL FROM tkl_proposals a WHERE
b.proposal_id=a.proposal_id AND
b.oc_initial=a.oc_initial AND
b.oc_place=a.oc_place)
)
AND b.oc_initial='MD'
AND b.oc_place='F'
AND b.proposal_date >= '1-1-2005'Zulu
You join two tables on varchar columns which have a different collation.
Table1.LastName=Table2.surname COLLATE HEBREW_CI_AS (Change it for your
needs)
That means Table2 has not the same collation as Table1 and by using above
hints I define to use this praticular collation.
"zulu" <zkendir@.simternet.com> wrote in message
news:O2ZXLyeCFHA.2876@.TK2MSFTNGP12.phx.gbl...
> Why do i get the error below:
> Server: Msg 446, Level 16, State 9, Line 1
> Cannot resolve collation conflict for equal to operation.
> What should i do?
>
>
> INSERT INTO tkl_proposals(
> proposal_id, username, oc_group, oc_place, oc_initial, owner_name,
> front_page_tr, front_page_en, notes, owner_company_id, status,
> proposal_date, transfer_date, from_server)
> SELECT
> proposal_id, username, oc_group, oc_place, oc_initial, owner_name,
> front_page_tr, front_page_en, notes, owner_company_id, status,
> proposal_date, getdate(), 'KIRMIZI'
> FROM MD_F.MidasLocal.dbo.tkl_proposals b
> WHERE
> (NOT EXISTS (SELECT NULL FROM tkl_proposals a WHERE
> b.proposal_id=a.proposal_id AND
> b.oc_initial=a.oc_initial AND
> b.oc_place=a.oc_place)
> )
> AND b.oc_initial='MD'
> AND b.oc_place='F'
> AND b.proposal_date >= '1-1-2005'
>|||> What should i do?
It's a good practice to provide DDL (CREATE TABLE statements) so that we
can better help you.
This error may be due do different collations on the joined columns. You
can explicitly specify the desired collation using a COLLATE clause in your
query. See the Bools Online for more information.
Hope this helps.
Dan Guzman
SQL Server MVP
"zulu" <zkendir@.simternet.com> wrote in message
news:O2ZXLyeCFHA.2876@.TK2MSFTNGP12.phx.gbl...
> Why do i get the error below:
> Server: Msg 446, Level 16, State 9, Line 1
> Cannot resolve collation conflict for equal to operation.
> What should i do?
>
>
> INSERT INTO tkl_proposals(
> proposal_id, username, oc_group, oc_place, oc_initial, owner_name,
> front_page_tr, front_page_en, notes, owner_company_id, status,
> proposal_date, transfer_date, from_server)
> SELECT
> proposal_id, username, oc_group, oc_place, oc_initial, owner_name,
> front_page_tr, front_page_en, notes, owner_company_id, status,
> proposal_date, getdate(), 'KIRMIZI'
> FROM MD_F.MidasLocal.dbo.tkl_proposals b
> WHERE
> (NOT EXISTS (SELECT NULL FROM tkl_proposals a WHERE
> b.proposal_id=a.proposal_id AND
> b.oc_initial=a.oc_initial AND
> b.oc_place=a.oc_place)
> )
> AND b.oc_initial='MD'
> AND b.oc_place='F'
> AND b.proposal_date >= '1-1-2005'
>|||ok, got it, thank you
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:O$CfLGfCFHA.4052@.TK2MSFTNGP15.phx.gbl...
> Zulu
> You join two tables on varchar columns which have a different collation.
> Table1.LastName=Table2.surname COLLATE HEBREW_CI_AS (Change it for your
> needs)
> That means Table2 has not the same collation as Table1 and by using above
> hints I define to use this praticular collation.
>
> "zulu" <zkendir@.simternet.com> wrote in message
> news:O2ZXLyeCFHA.2876@.TK2MSFTNGP12.phx.gbl...
>

COLLATE problem

Why do i get the error below:
Server: Msg 446, Level 16, State 9, Line 1
Cannot resolve collation conflict for equal to operation.
What should i do?
INSERT INTO tkl_proposals(
proposal_id, username, oc_group, oc_place, oc_initial, owner_name,
front_page_tr, front_page_en, notes, owner_company_id, status,
proposal_date, transfer_date, from_server)
SELECT
proposal_id, username, oc_group, oc_place, oc_initial, owner_name,
front_page_tr, front_page_en, notes, owner_company_id, status,
proposal_date, getdate(), 'KIRMIZI'
FROM MD_F.MidasLocal.dbo.tkl_proposals b
WHERE
(NOT EXISTS (SELECT NULL FROM tkl_proposals a WHERE
b.proposal_id=a.proposal_id AND
b.oc_initial=a.oc_initial AND
b.oc_place=a.oc_place)
)
AND b.oc_initial='MD'
AND b.oc_place='F'
AND b.proposal_date >= '1-1-2005'Zulu
You join two tables on varchar columns which have a different collation.
Table1.LastName=Table2.surname COLLATE HEBREW_CI_AS (Change it for your
needs)
That means Table2 has not the same collation as Table1 and by using above
hints I define to use this praticular collation.
"zulu" <zkendir@.simternet.com> wrote in message
news:O2ZXLyeCFHA.2876@.TK2MSFTNGP12.phx.gbl...
> Why do i get the error below:
> Server: Msg 446, Level 16, State 9, Line 1
> Cannot resolve collation conflict for equal to operation.
> What should i do?
>
>
> INSERT INTO tkl_proposals(
> proposal_id, username, oc_group, oc_place, oc_initial, owner_name,
> front_page_tr, front_page_en, notes, owner_company_id, status,
> proposal_date, transfer_date, from_server)
> SELECT
> proposal_id, username, oc_group, oc_place, oc_initial, owner_name,
> front_page_tr, front_page_en, notes, owner_company_id, status,
> proposal_date, getdate(), 'KIRMIZI'
> FROM MD_F.MidasLocal.dbo.tkl_proposals b
> WHERE
> (NOT EXISTS (SELECT NULL FROM tkl_proposals a WHERE
> b.proposal_id=a.proposal_id AND
> b.oc_initial=a.oc_initial AND
> b.oc_place=a.oc_place)
> )
> AND b.oc_initial='MD'
> AND b.oc_place='F'
> AND b.proposal_date >= '1-1-2005'
>|||> What should i do?
It's a good practice to provide DDL (CREATE TABLE statements) so that we
can better help you.
This error may be due do different collations on the joined columns. You
can explicitly specify the desired collation using a COLLATE clause in your
query. See the Bools Online for more information.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"zulu" <zkendir@.simternet.com> wrote in message
news:O2ZXLyeCFHA.2876@.TK2MSFTNGP12.phx.gbl...
> Why do i get the error below:
> Server: Msg 446, Level 16, State 9, Line 1
> Cannot resolve collation conflict for equal to operation.
> What should i do?
>
>
> INSERT INTO tkl_proposals(
> proposal_id, username, oc_group, oc_place, oc_initial, owner_name,
> front_page_tr, front_page_en, notes, owner_company_id, status,
> proposal_date, transfer_date, from_server)
> SELECT
> proposal_id, username, oc_group, oc_place, oc_initial, owner_name,
> front_page_tr, front_page_en, notes, owner_company_id, status,
> proposal_date, getdate(), 'KIRMIZI'
> FROM MD_F.MidasLocal.dbo.tkl_proposals b
> WHERE
> (NOT EXISTS (SELECT NULL FROM tkl_proposals a WHERE
> b.proposal_id=a.proposal_id AND
> b.oc_initial=a.oc_initial AND
> b.oc_place=a.oc_place)
> )
> AND b.oc_initial='MD'
> AND b.oc_place='F'
> AND b.proposal_date >= '1-1-2005'
>|||ok, got it, thank you
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:O$CfLGfCFHA.4052@.TK2MSFTNGP15.phx.gbl...
> Zulu
> You join two tables on varchar columns which have a different collation.
> Table1.LastName=Table2.surname COLLATE HEBREW_CI_AS (Change it for your
> needs)
> That means Table2 has not the same collation as Table1 and by using above
> hints I define to use this praticular collation.
>
> "zulu" <zkendir@.simternet.com> wrote in message
> news:O2ZXLyeCFHA.2876@.TK2MSFTNGP12.phx.gbl...
> > Why do i get the error below:
> >
> > Server: Msg 446, Level 16, State 9, Line 1
> > Cannot resolve collation conflict for equal to operation.
> >
> > What should i do?
> >
> >
> >
> >
> >
> > INSERT INTO tkl_proposals(
> > proposal_id, username, oc_group, oc_place, oc_initial, owner_name,
> > front_page_tr, front_page_en, notes, owner_company_id, status,
> > proposal_date, transfer_date, from_server)
> > SELECT
> > proposal_id, username, oc_group, oc_place, oc_initial, owner_name,
> > front_page_tr, front_page_en, notes, owner_company_id, status,
> > proposal_date, getdate(), 'KIRMIZI'
> > FROM MD_F.MidasLocal.dbo.tkl_proposals b
> > WHERE
> > (NOT EXISTS (SELECT NULL FROM tkl_proposals a WHERE
> > b.proposal_id=a.proposal_id AND
> > b.oc_initial=a.oc_initial AND
> > b.oc_place=a.oc_place)
> > )
> > AND b.oc_initial='MD'
> > AND b.oc_place='F'
> > AND b.proposal_date >= '1-1-2005'
> >
> >
>

cmdsql - Create a text file without Informational Messages

Hello all

I have a sql file that I want execute by using the cmdsql command line. But when I create a text file I receive two Informational Messages:

1."Changed database context to 'DataBaseName'."

2.(2 row(s) affected)

How can I ignore these messages in my text file? there is a parametter or something elese to configure to avoir these Informational Messages?

Sorry is not cmdsql is Sqlcmd :)

CmdExec Job Step in Sql Server2000

Thanks for the invitation to post a question, so I will post one.

I need to create a job step that uses cmdExec.

This is the command line I entering:
D:\odbc\TimeClockUpdates\bin\Release\TimeKeepingNo nLogouts.exe

When I run this job the job fails. When I look at the job history, the only information I get is the date and time, user that ran the job and the fact that it failed. I haven't been able to get any CmdExec job to run at all. Can anyone tell me what I'm doing wrong?

Facts.
1. This exact same command is used by my network administrator using windows scheduler on the server. The only reason he wants me to create an sql server job is because it's mostly sql functions.
2. I know the Sql Server agent is running, because I have other jobs that are run.
3. I have verified that I have permission to run the file because I can go to the actual directory and run the exe.
4. Do I need to enclose my command in quotes i.e. D:\odbc\TimeClockUpdates\bin\Release\TimeKeepingN onLogouts.exe
5. the path of the file I need to run is the path on the server and not the path on my local machine.

If you need any other information, please let me know
Thanks for you help
GEMyou should post the code.

cmdexec requires that the security account executing job have sysadmin permissions.|||I wish I knew what code you mean me to post

This is a job I created using enterprise manager to create the job.
1. I entered the Job Name: UpdateEmployeeLogins
2. Job type: Operation System Command (CmdExec)
3. Command: D:\odbc\TimeClockUpdates\bin\Release\TimeKeepingNo nLogouts.exe

I did all this through Enterprise Manager. What code do you want me to post?

Thanks
GEM|||change the command type to t-sql and try this...

EXEC xp_cmdshell 'D:\odbc\TimeClockUpdates\bin\Release\TimeKeepingN o nLogouts.exe'|||I did as you suggested and but the same result. I then ran that command line with query analyzer and got an "Access Denied" Error so that means I must have a problem somewhere with permissions. This doesn't make a whole lot of sense since I can the exe when clicking on it. I will have to get with out network administrator who's not available right now.

Your help was excellent, I hope you'll be around later to help me with this if I need it.

Thanks
GEM|||see my 1st post.|||This is a job I created using enterprise manager to create the job.
1. I entered the Job Name: UpdateEmployeeLogins
2. Job type: Operation System Command (CmdExec)
3. Command: D:\odbc\TimeClockUpdates\bin\Release\TimeKeepingNo nLogouts.exe

This method should work. It should not be necessary to use xp_cmdshell in your job.
As Thrassy stated, this is likely due to a permissions issue regarding the account that the SQL Agent service is running under.|||As everyone else said, make sure that the account executing the command under the SQL service privileges has the appropriate access to the executable. I bet if you look under NTFS security on the target .exe you'll find the problem.

clustered vs non clustered indexes

can someone bottom line this for me? whats the difference between the two
and how do I know when to use which type?
any info is greatly appreciated. Thanks.
The first place to look for information about sql server is in the online
documentation (BOL - Books Online).
Creating and Maintaining Databases/Indexes/Designing an Index/Using
Clustered Indexes
This section has a wealth of information about indexes. Try giving that a
thorough reading.
"djc" <noone@.nowhere.com> wrote in message
news:uFq3wSlmEHA.324@.TK2MSFTNGP11.phx.gbl...
> can someone bottom line this for me? whats the difference between the two
> and how do I know when to use which type?
> any info is greatly appreciated. Thanks.
>
|||As Scott states you should read up on it but the main difference is that you
can only have one clustered index per table since it physically sorts the
data in the order of the column(s) in the index expression. The leaf level
(lowest level) of the clustered index is the actual row of data where as in
a non-clustered index the leaf level points to the key in the clustered
index. Most tables should have a clustered index if for no other reason as
that is the only way to control fragmentation and avoid forwarding pointers.
But a CI can be extremely useful with lookups especially if you have range
type queries that use the expression the Ci is built on.
Andrew J. Kelly SQL MVP
"djc" <noone@.nowhere.com> wrote in message
news:uFq3wSlmEHA.324@.TK2MSFTNGP11.phx.gbl...
> can someone bottom line this for me? whats the difference between the two
> and how do I know when to use which type?
> any info is greatly appreciated. Thanks.
>
|||Thanks Scott. I may have to do that but I was hoping for one of the experts
here to bottom line it for me since I don't need all the detail yet. I'm
just being resourceful.
Thanks for the reply though.
"Scott Morris" <bogus@.bogus.com> wrote in message
news:%233J0%23hlmEHA.2864@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> The first place to look for information about sql server is in the online
> documentation (BOL - Books Online).
> Creating and Maintaining Databases/Indexes/Designing an Index/Using
> Clustered Indexes
> This section has a wealth of information about indexes. Try giving that a
> thorough reading.
> "djc" <noone@.nowhere.com> wrote in message
> news:uFq3wSlmEHA.324@.TK2MSFTNGP11.phx.gbl...
two
>
|||That will do. Thank you very much!
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:ODyJ5ulmEHA.2764@.TK2MSFTNGP11.phx.gbl...
> As Scott states you should read up on it but the main difference is that
you
> can only have one clustered index per table since it physically sorts the
> data in the order of the column(s) in the index expression. The leaf
level
> (lowest level) of the clustered index is the actual row of data where as
in
> a non-clustered index the leaf level points to the key in the clustered
> index. Most tables should have a clustered index if for no other reason
as
> that is the only way to control fragmentation and avoid forwarding
pointers.[vbcol=seagreen]
> But a CI can be extremely useful with lookups especially if you have range
> type queries that use the expression the Ci is built on.
> --
> Andrew J. Kelly SQL MVP
>
> "djc" <noone@.nowhere.com> wrote in message
> news:uFq3wSlmEHA.324@.TK2MSFTNGP11.phx.gbl...
two
>

clustered vs non clustered indexes

can someone bottom line this for me? whats the difference between the two
and how do I know when to use which type?
any info is greatly appreciated. Thanks.The first place to look for information about sql server is in the online
documentation (BOL - Books Online).
Creating and Maintaining Databases/Indexes/Designing an Index/Using
Clustered Indexes
This section has a wealth of information about indexes. Try giving that a
thorough reading.
"djc" <noone@.nowhere.com> wrote in message
news:uFq3wSlmEHA.324@.TK2MSFTNGP11.phx.gbl...
> can someone bottom line this for me? whats the difference between the two
> and how do I know when to use which type?
> any info is greatly appreciated. Thanks.
>|||As Scott states you should read up on it but the main difference is that you
can only have one clustered index per table since it physically sorts the
data in the order of the column(s) in the index expression. The leaf level
(lowest level) of the clustered index is the actual row of data where as in
a non-clustered index the leaf level points to the key in the clustered
index. Most tables should have a clustered index if for no other reason as
that is the only way to control fragmentation and avoid forwarding pointers.
But a CI can be extremely useful with lookups especially if you have range
type queries that use the expression the Ci is built on.
--
Andrew J. Kelly SQL MVP
"djc" <noone@.nowhere.com> wrote in message
news:uFq3wSlmEHA.324@.TK2MSFTNGP11.phx.gbl...
> can someone bottom line this for me? whats the difference between the two
> and how do I know when to use which type?
> any info is greatly appreciated. Thanks.
>|||Thanks Scott. I may have to do that but I was hoping for one of the experts
here to bottom line it for me since I don't need all the detail yet. I'm
just being resourceful.
Thanks for the reply though.
"Scott Morris" <bogus@.bogus.com> wrote in message
news:%233J0%23hlmEHA.2864@.tk2msftngp13.phx.gbl...
> The first place to look for information about sql server is in the online
> documentation (BOL - Books Online).
> Creating and Maintaining Databases/Indexes/Designing an Index/Using
> Clustered Indexes
> This section has a wealth of information about indexes. Try giving that a
> thorough reading.
> "djc" <noone@.nowhere.com> wrote in message
> news:uFq3wSlmEHA.324@.TK2MSFTNGP11.phx.gbl...
> > can someone bottom line this for me? whats the difference between the
two
> > and how do I know when to use which type?
> >
> > any info is greatly appreciated. Thanks.
> >
> >
>|||That will do. Thank you very much!
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:ODyJ5ulmEHA.2764@.TK2MSFTNGP11.phx.gbl...
> As Scott states you should read up on it but the main difference is that
you
> can only have one clustered index per table since it physically sorts the
> data in the order of the column(s) in the index expression. The leaf
level
> (lowest level) of the clustered index is the actual row of data where as
in
> a non-clustered index the leaf level points to the key in the clustered
> index. Most tables should have a clustered index if for no other reason
as
> that is the only way to control fragmentation and avoid forwarding
pointers.
> But a CI can be extremely useful with lookups especially if you have range
> type queries that use the expression the Ci is built on.
> --
> Andrew J. Kelly SQL MVP
>
> "djc" <noone@.nowhere.com> wrote in message
> news:uFq3wSlmEHA.324@.TK2MSFTNGP11.phx.gbl...
> > can someone bottom line this for me? whats the difference between the
two
> > and how do I know when to use which type?
> >
> > any info is greatly appreciated. Thanks.
> >
> >
>

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!
>
>

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 multipl
e
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 fo
r
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 som
e
> cases where a clustered index does not work this way, and the only solutio
n
> 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. Th
e
> 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 querie
s
> 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 a
nd
> was recreated on a copy of the database on another server, also with multi
ple
> 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:
[vbcol=seagreen]
> Correction: the query on TableA using LIKE should read this:
> Select * from TableA Where Col2 LIKE 'SOMEVALUE%'
> "Mike K" wrote:
>|||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...[vbcol=seagreen]
> 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:
>|||> 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 som
e
> cases where a clustered index does not work this way, and the only solutio
n
> 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. Th
e
> 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 querie
s
> 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 a
nd
> was recreated on a copy of the database on another server, also with multi
ple
> 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 inde
x
hadn't been there. This interpretation agrees with everyone's responses.
"Gail Erickson [MS]" wrote:

> Do you happen to remember which topic(s) you looked at? This is inaccura
te
> 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 ga
ve
> 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...
>
>

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:
[vbcol=seagreen]
> Correction: the query on TableA using LIKE should read this:
> Select * from TableA Where Col2 LIKE 'SOMEVALUE%'
> "Mike K" wrote:
|||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...[vbcol=seagreen]
> 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:
|||> 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:

> 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...
>
>