|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
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...? > SELECT TOP 10 * FROM <TABLE_NAME> Always use ORDER BY with TOP:> > 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?? 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 MUKUT (hidev***@gmail.com) writes:
> Not any project specific but just a beginner's query put me at As long as there is on ORDER BY clause, the answer is simply "the rows > 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?? 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, esq***@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx Erland Sommarskog wrote:
> As long as there is on ORDER BY clause, the answer is simply "the rows Ya, I understood the significance of using ORDER BY with TOP...........> 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). > 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 confused that how sqlserver2000 interprets the Select TOP 20 * from <TABLE_NAME>....!!!! > No disrespect at all, but Erland, would you please brief on the The first part states that you can execute the same query several times and what rows you are > statement > "The result is not deterministic, although it's neither random." 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 No, not random. At least of you go by a strict definition of what random mean. What happen is that > expected to be in random order. 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. -- Show quoteHide quoteTibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ "MUKUT" <hidev***@gmail.com> wrote in message news:1149766325.373514.173190@i39g2000cwa.googlegroups.com... > > 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 clause >> serves to give the TOP clause a deterministic meaning (save for ties, unless >> 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 confused 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 The main problem is that, since you often get the SAME execution plan, > 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. 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 True. I've always thought they should have named it STOPAFTER...> the way sqlserver2000 behaves...:):) -- Show quoteHide quoteTibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ "MUKUT" <hidev***@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. >  
Other interesting topics
Help! Users can't connect to database
Help with stored procedure using "IN" Bring back Query Analyser SQL2005: "Registered Owner" and "Registered Organization" available? which query is more efficent? (oppinion time!) Stored Procedure Help Use of SELECT...,0 FROM Select all Rows on distinct only on one column Dynamically use variables in SQL in EXECUTE Need help with this |
|||||||||||||||||||||||
