problem with a clustered index?

Hello,
I have a table with about 5 milion records, and the performance is un-acceptable (more than 45 seconds for a query). The table was imported from a text file. I have defined a primary key (two varchar(20) columns). Then I checked and the database has created a clustered index on these columns. But I suspect that something is wrong with the index: When I run a select query, the rows are returned in an arbitrary order, and not in the order defined by the index. Is it possible that something is wrong with the index? If yes, how can I fix that?
thanks, David
David
Did you spesify ORDER BY clause when you run SELECT statement.
What is your search criteria in the query?
"David" <dboaz@.bgumail.bgu.ac.il> wrote in message news:um00pGXdEHA.3148@.TK2MSFTNGP10.phx.gbl...
Hello,
I have a table with about 5 milion records, and the performance is un-acceptable (more than 45 seconds for a query). The table was imported from a text file. I have defined a primary key (two varchar(20) columns). Then I checked and the database has created a clustered index on these columns. But I suspect that something is wrong with the index: When I run a select query, the rows are returned in an arbitrary order, and not in the order defined by the index. Is it possible that something is wrong with the index? If yes, how can I fix that?
thanks, David
|||A clustered index does not determine the sort order of a query result, so
there is no reason to suspect any integrity problem. Use an ORDER BY clause
in your SELECT statement to fix the order of the returned rows.
Did you check the query plan to see what indexes are being used? Could you
post the query and a CREATE TABLE statement for the table to give us an idea
of what optimizations might be possible.
David Portas
SQL Server MVP
|||> When I run a select query, the rows are returned in an arbitrary order
A table is an unordered set of rows. What were you expecting? If you want
a defined order, use an ORDER BY clause.
I don't know where everyone gets the idea that the existence of a clustered
index means that is the order all SELECT queries will return the data. This
is NOT TRUE! It does happen more often that way, but it is not a law. The
plan will return the rows in the best way it sees fit, you could run the
query 10 times and it *could* return the rows 10 different ways. Usually
doesn't, but could.
I'll repeat: if you want a specific order, use an ORDER BY clause.
http://www.aspfaq.com/
(Reverse address to reply.)