March 22, 2002 at 4:32 pm
All,
I'm having a problem understanding the way a stored procedure is reacting. I am trying to return the first 25 rows from a query that joins 3 tables. Originally I used an order by statement, but because I was using the 'set rowcount 25' command, it was taking a long time to process. I created a clustered index on the column which I was ordering by. Now the query is much faster, but it doesn't always return the rusults in the order I expect, which is the order of the clustered index.
What am I missing? The other two tables in the join are clustered on the same key.
Any ideas or explainations will be appreciated.
Thanks,
Ken C.
March 23, 2002 at 6:29 am
From BOL
quote:
Setting the SET ROWCOUNT option causes most Transact-SQL statements to stop processing when they have been affected by the specified number of rows.
What I have always took this to mean is that as soon as the the compiler reaches 25 records return in the join it will stop and output the data. This means that no matter what order the cluster index has on one table the other two tables will join their data on the coparable object and say you have a identity cluster and table 2 returns first the 5th record matched then the 12th then the 1st then 8th and so on until 25 records enter the cache.
Generally I use TOP 25 instead and do order by the clustered index column I want the order to be. The reason is TOP will return the number of records based on the after join and ordering.
Now from what you have said I would have assumed the order would have been by the cluster since you state the remotes tables are clustered on the matching column. Sorry I have never tested what happens with SET ROWCOUNT on that type of situation so cannot give a summation of what is happening. The question is what did you have and what did you see as opposed to expect?
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply