HOW "TOP" is interpreted?

All,
Not any project specific but just a beginner's query put me at
corner.
SELECT TOP 10 * FROM <TABLE_NAME>
Sqlserver2000 returns 10 results, but the question is "these 10 results
are top 10 results" means what? There are 100s columns in the table
with lacks of rows. Among them how this 10 results were ranked'
Any suggestions...?MUKUT (hidevraj@.gmail.com) writes:
> Not any project specific but just a beginner's query put me at
> corner.
> SELECT TOP 10 * FROM <TABLE_NAME>
> Sqlserver2000 returns 10 results, but the question is "these 10 results
> are top 10 results" means what? There are 100s columns in the table
> with lacks of rows. Among them how this 10 results were ranked'
As long as there is on ORDER BY clause, the answer is simply "the rows
that are cheapest for SQL Server to return". Thus, the result is not
deterministic, although it's neither random.
Normally you combine TOP with an ORDER BY in which case the ORDER BY clause
serves to give the TOP clause a deterministic meaning (save for ties, unless
you specify WITH TIES).
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||> SELECT TOP 10 * FROM <TABLE_NAME>
> Sqlserver2000 returns 10 results, but the question is "these 10 results
> are top 10 results" means what? There are 100s columns in the table
> with lacks of rows. Among them how this 10 results were ranked'
Always use ORDER BY with TOP:
SELECT TOP 10 * FROM <TABLE_NAME>
ORDER BY column (ASC / DESC)
Now you know waht top 10 means.
Dejan Sarka, SQL Server MVP
Mentor, www.SolidQualityLearning.com
Anything written in this message represents solely the point of view of the
sender.
This message does not imply endorsement from Solid Quality Learning, and it
does not represent the point of view of Solid Quality Learning or any other
person, company or institution mentioned in this message|||Erland Sommarskog wrote:
> As long as there is on ORDER BY clause, the answer is simply "the rows
> that are cheapest for SQL Server to return". Thus, the result is not
> deterministic, although it's neither random.
> Normally you combine TOP with an ORDER BY in which case the ORDER BY claus
e
> serves to give the TOP clause a deterministic meaning (save for ties, unle
ss
> you specify WITH TIES).
>
Ya, I understood the significance of using ORDER BY with TOP.........
No disrespect at all, but Erland, would you please brief on the
statement
"The result is not deterministic, although it's neither random."
I aware to guess... when there is no ORDER BY.. the results are
expected to be in random order.
And still I am that how sqlserver2000 interprets the
Select TOP 20 * from <TABLE_NAME>...!!!!|||> No disrespect at all, but Erland, would you please brief on the
> statement
> "The result is not deterministic, although it's neither random."
The first part states that you can execute the same query several times and
what rows you are
returned are not guaranteed in any way to be the same rows. The second part
states that SQL Server
doesn't add some random number generator to return rows in a random fashion.

> I aware to guess... when there is no ORDER BY.. the results are
> expected to be in random order.
No, not random. At least of you go by a strict definition of what random mea
n. What happen is that
you ask for, say, 10 rows. Any rows from the table, you don't care which row
s are returned. So, SQL
Server will pick an execution plan which is as cheap as possible to return 1
0 rows. This is why it
isn't deterministic, you can get different execution plans for different exe
cutions.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"MUKUT" <hidevraj@.gmail.com> wrote in message
news:1149766325.373514.173190@.i39g2000cwa.googlegroups.com...
> Erland Sommarskog wrote:
> Ya, I understood the significance of using ORDER BY with TOP.........
> No disrespect at all, but Erland, would you please brief on the
> statement
> "The result is not deterministic, although it's neither random."
> I aware to guess... when there is no ORDER BY.. the results are
> expected to be in random order.
> And still I am that how sqlserver2000 interprets the
> Select TOP 20 * from <TABLE_NAME>...!!!!
>|||> No, not random. At least of you go by a strict definition of what random
> mean. What happen is that you ask for, say, 10 rows. Any rows from the
> table, you don't care which rows are returned. So, SQL Server will pick an
> execution plan which is as cheap as possible to return 10 rows. This is
> why it isn't deterministic, you can get different execution plans for
> different executions.
The main problem is that, since you often get the SAME execution plan,
people think this is the intended behavior, and you will *always* get the
same execution plan. Which, of course, we know is not true. But it still
leaves a lot of people lazy, leaving out the ORDER BY clause, expecting it
to behave the same with or without it. Sorry for those people that have to
search and modify all their procedures with "TOP" when they migrate to SQL
Server 2005, which tends to "break" this behavior more often than not. :-)|||When there is no ORDER BY, the dictionary meaning of TOP is hampered by
the way sqlserver2000 behaves...:):)
Anyway, things are clear to me...thank you all for your time.|||> When there is no ORDER BY, the dictionary meaning of TOP is hampered by
> the way sqlserver2000 behaves...:):)
True. I've always thought they should have named it STOPAFTER...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"MUKUT" <hidevraj@.gmail.com> wrote in message
news:1149827146.025175.234160@.i39g2000cwa.googlegroups.com...
> When there is no ORDER BY, the dictionary meaning of TOP is hampered by
> the way sqlserver2000 behaves...:):)
> Anyway, things are clear to me...thank you all for your time.
>