|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
SET ROWCOUNT!---------------------------------------- The SET ROWCOUNT limit applies to building the rows in the result set before an ORDER BY is evaluated. Even if ORDER BY is specified, the SELECT statement is terminated when n rows have been selected. n rows are selected, then ordered and returned to the client. ---------------------------------------- If I haven't misinterpreted the above, this means that the following 2 queries should return the same resultset (the only difference between the 2 queries is the ColumName used in the ORDER BY clause): ---------------------------------------- SET ROWCOUNT 5 SELECT * FROM Products ORDER BY UnitPrice GO ---------------------------------------- ---------------------------------------- SET ROWCOUNT 5 SELECT * FROM Products ORDER BY ProductName GO ---------------------------------------- but the above 2 queries return different resultset! Why so? Arpan
No, it returned five rows for both select statements It is similar how TOP clause works to return the data and specify ORDER BY clause Show quote "Arpan" <arpan***@hotmail.com> wrote in message news:1123825059.815462.10290@g49g2000cwa.googlegroups.com... > In BOL, it is stated that > > ---------------------------------------- > The SET ROWCOUNT limit applies to building the rows in the result set > before an ORDER BY is evaluated. Even if ORDER BY is specified, the > SELECT statement is terminated when n rows have been selected. n rows > are selected, then ordered and returned to the client. > ---------------------------------------- > > If I haven't misinterpreted the above, this means that the following 2 > queries should return the same resultset (the only difference between > the 2 queries is the ColumName used in the ORDER BY clause): > > ---------------------------------------- > SET ROWCOUNT 5 > SELECT * FROM Products ORDER BY UnitPrice > GO > ---------------------------------------- > > ---------------------------------------- > SET ROWCOUNT 5 > SELECT * FROM Products ORDER BY ProductName > GO > ---------------------------------------- > > but the above 2 queries return different resultset! Why so? > Arpan wrote:
> "[...] The SET ROWCOUNT limit applies to building the rows in the result set before an ORDER BY is evaluated. [...]"Where did you read this statement ? In the "Limiting Result Sets Using TOP and PERCENT" topic ? If so, that statement was incorrect in earlier versions of Books Online. It was corrected in the last version of Books Online, where it reads: The SET ROWCOUNT limit applies to building the rows in the result set after an ORDER BY is evaluated. When ORDER BY is specified, the SELECT statement is terminated when n rows have been selected from a set of values that has been sorted according to specified ORDER BY classification. For more informations, see: http://msdn.microsoft.com/library/en-us/acdata/ac_8_qd_04_13ec.asp http://www.microsoft.com/sql/techinfo/productdoc/2000/books.mspx Razvan Thanks, Razvan, for your input. You have hit the nail on the head.....I
quoted that statement from the topic 'Limiting Result Sets Using TOP and PERCENT" in BOL! I was utterly confused reading that errorneous statement but now my confusion has been laid to rest. So does that imply that the one & only one difference between TOP & SET ROWCOUNT is that TOP applies to the single SELECT query in which it is specified whereas SET ROWCOUNT remains in effect until another SET ROWCOUNT statement is executed? Thanks once again....& thanks to you too, Uri Regards, Arpan > So does that imply that the one & only one difference between TOP & SET (Some of) the other differences:> ROWCOUNT is that TOP applies to the single SELECT query in which it is > specified whereas SET ROWCOUNT remains in effect until another SET > ROWCOUNT statement is executed? 1. TOP can be used in a subquery, but ROWCOUNT cannot; 2. TOP accepts only a constant, ROWCOUNT also accepts a variable; This will change in SQL Server 2005, where TOP will accept an expression. 3. TOP can be used only with SELECT-s, but ROWCOUNT also affects data modification statements, such as UPDATE-s and DELETE-s. This will also change in SQL Server 2005, where TOP will be possible in UPDATE-s and DELETE-s, but the use of SET ROWCOUNT in conjunction with the INSERT, UPDATE and DELETE statements is being deprecated. The use of SET ROWCOUNT regarding SELECT-s is supported, but in the next release of SQL Server (after SQL Server 2005), using SET ROWCOUNT will not affect DELETE, INSERT, and UPDATE statements. Razvan Also, the optimizer has a chance to pick a better plan for TOP as the number of rows is known early
in the optimization phase, just as it cannot be a variable. This has been debated here some years ago, and the conclusion was that it is pretty difficult to come up with such an example. But as I recall, someone at least found one example where the plans differs. Search the archives if you want to try find it. I'd also like to mention one warning on ROWCOUNT. Don't forget to reset it. I've done that, and it isn't easy to find and understand that bug where some totally unrelated piece of code returns or modifies incorrectly because an old ROWCOUNT is there. -- Show quoteTibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ Blog: http://solidqualitylearning.com/blogs/tibor/ "Razvan Socol" <rso***@gmail.com> wrote in message news:1123832437.849414.163390@g47g2000cwa.googlegroups.com... >> So does that imply that the one & only one difference between TOP & SET >> ROWCOUNT is that TOP applies to the single SELECT query in which it is >> specified whereas SET ROWCOUNT remains in effect until another SET >> ROWCOUNT statement is executed? > > (Some of) the other differences: > > 1. TOP can be used in a subquery, but ROWCOUNT cannot; > > 2. TOP accepts only a constant, ROWCOUNT also accepts a variable; > This will change in SQL Server 2005, where TOP will accept an > expression. > > 3. TOP can be used only with SELECT-s, but ROWCOUNT also affects data > modification statements, such as UPDATE-s and DELETE-s. This will also > change in SQL Server 2005, where TOP will be possible in UPDATE-s and > DELETE-s, but the use of SET ROWCOUNT in conjunction with the INSERT, > UPDATE and DELETE statements is being deprecated. The use of SET > ROWCOUNT regarding SELECT-s is supported, but in the next release of > SQL Server (after SQL Server 2005), using SET ROWCOUNT will not affect > DELETE, INSERT, and UPDATE statements. > > Razvan > As Razvan explained, there is a correction:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_qd_04_13ec.asp However that may be, your queries may not even be deterministic or repeatable in themselves so it's unwise to expect them to return the same results. SET ROWCOUNT is applied after the sort, but that means you can only expect repeatable results if the defined sort order yields a unique result from your table. That seems unlikely to be the case when ordering by UnitPrice. If the data never changes then there may be a good chance that you'll get the same results each time you run the ORDER BY UnitPrice query but that result certainly isn't guaranteed unless each product has a unique price. Without ORDER BY, the results are of course completely undefined. You may get ANY 5 rows returned so there's no sense expecting the two queries to be equivalent. -- David Portas SQL Server MVP -- Yes, David, you are absolutely correct.....it indeed doesn't make any
sense to expect the 2 different queries to return the same resultset but that's what the errorneous statement in BOL implied & that is what confused me! Thanks, Regards, Arpan I'm nitpicking here, but I'm finding the distinction important:
The original statement (which we've agreed on is incorrect) doesn't state that they should return the same rows. It states that it is undefined which rows are returned. I.e., the optimizer is free to drive the query any ways it finds suitable and stop after 5 rows. -- Show quoteTibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ Blog: http://solidqualitylearning.com/blogs/tibor/ "Arpan" <arpan***@hotmail.com> wrote in message news:1123830418.228409.129160@f14g2000cwb.googlegroups.com... > Yes, David, you are absolutely correct.....it indeed doesn't make any > sense to expect the 2 different queries to return the same resultset > but that's what the errorneous statement in BOL implied & that is what > confused me! > > Thanks, > > Regards, > > Arpan >  |
|||||||||||||||||||||||
Other interesting topics