Added a Clustered index On ID cloumn but not ordered? Why?

Question

Post reply

Added a Clustered index On ID cloumn but not ordered? Why?

thinknight

SSCrazy

Points: 2068
More actions
May 10, 2011 at 9:13 am

#235995

Hi All,
I added Clustered Index to ID Cloumn on Testtable, after adding index records did not ordered iwth ID cloumn, Why?
Clustered Index On TestTable.ID
Query:
select ID from TestTable;
Result:
ID
-----
7
9
12
3
5
11

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

i don't believe the index was added.

this works fine for me, and orders the results as expected int eh second query:

CREATE TABLE TestTable(ID INT);

GO

INSERT INTO TestTable(ID)

SELECT 7 UNION ALL

SELECT 9 UNION ALL

SELECT 12 UNION ALL

SELECT 3 UNION ALL

SELECT 5 UNION ALL

SELECT 11;

GO

select ID from TestTable;

GO

CREATE CLUSTERED INDEX IX_TESTTABLE On TestTable(ID)

GO

select ID from TestTable;

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

thinknight SSCrazy Points: 2068 More actions · Answer 2

There are many nonclustered index on different columns on same table and also pk is different. Does it effect this situation?

Syed Jahanzaib Bin hassan SSChampion Points: 11410 More actions · Answer 3

Data Type of ID column is ?

My Blog
www.aureus-salah.com

thinknight SSCrazy Points: 2068 More actions · Answer 4

thinknight

SSCrazy

Points: 2068

May 10, 2011 at 9:30 am

#1322412

int

Lowell SSC Guru Points: 323518 More actions · Answer 5

thinknight (5/10/2011)
There are many nonclustered index on different columns on same table and also pk is different. Does it effect this situation?

that's not what you said before...

you said you added a clustered index on a specific column, and you don't get the results in order of the clustered index...assuming there is no WHERE statement or ORDER BY.

sounds like the conversion from real issue to pseudocode to describe the problem has also removed the details to identify the issue.

show us the actual CREATE TABLE statement so we can see the clustered index(if any)

Show us the actual query you are running, if it is different than what you posted.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

sharath.chalamgari SSCertifiable Points: 5680 More actions · Answer 6

if the data type is something like varchar then the cluster index will not provide the order when you retrive

Kenneth.Fisher SSC-Insane Points: 20396 More actions · Answer 7

Unless something has changed that I'm not aware of (more than possible), just because you have an index, even a clustered one, does not mean your data will come back in order unless you use an order by.

Everything I have ever read on SQL states pretty emphatically that SQL Server (any version) does not guarantee the order of the data unless you use an order by clause.

Kenneth

Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]

thinknight SSCrazy Points: 2068 More actions · Answer 8

Ok, After adding a where clause it was shown in order. Thanks for all.

Now, I understood case.

Syed Jahanzaib Bin hassan SSChampion Points: 11410 More actions · Answer 9

Create all non-clustered index scripts and then drop it all then create clustered index then create non-clustered index after clustered index

Note It when we create clustered index then non-clustered automatically recreated if exist on the same table

My Blog
www.aureus-salah.com

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 10

Actually we're all missing the big picture (Except Kenneth).

The order of the table is NOT garanteed. The only way to garantee it is to use ORDER BY. There's no exception to that rule.

Nils Gustav Stråbø SSChampion Points: 11259 More actions · Answer 11

What do you say? Is there an exception to where ORDER BY does not guarantee ordered results?

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 12

Nils Gustav Stråbø (5/10/2011)
What do you say? Is there an exception to where ORDER BY does not guarantee ordered results?

No I say that if you DON'T use order by in the query, then the query has no order. It might look like it does but it doesn't.

Adding or removing <clustered> index or not will have no effect on this behavior.

Nils Gustav Stråbø SSChampion Points: 11259 More actions · Answer 13

Sorry, I must have missed a word or two when I read your post :blush:

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 14

Nils Gustav Stråbø (5/10/2011)
Sorry, I must have missed a word or two when I read your post :blush:

No I miss-typed 1 word. Edited.