problem with a clustered index?

Hello,
I have a table with about 5 milion records, and the performance is un-accept
able (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 susp
ect that something is wrong with the index: When I run a select query, the r
ows 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 ca
n I fix that?
thanks, DavidDavid
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@.TK2
MSFTNGP10.phx.gbl...
Hello,
I have a table with about 5 milion records, and the performance is un-accept
able (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 susp
ect that something is wrong with the index: When I run a select query, the r
ows 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 ca
n 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.)sql