is the Top or Order by done first?
in [Programming SQL]
|
From: tshad on 22 Apr 2010 17:56 I have a SP that I am rewriting and has something like: ************************************* Set ROWCOUNT = 100 SELECT * FROM ( SELECT TOP 100 PERCENT * FROM #Temp AS T1 ) AS Q1 WHERE Name <> 'offset_name' ************************************** Not sure why he is doing this. In the derived table he has 280 records. In the outer table he is doing a SELECT on records that don't have Name = 'offset_name'. #Temp was created with a SELECT/INTO ordered by Name. I assume the ordering wouldn't mean anything in this select statement. Would the ROWCOUNT = 100 take affect after the rows were removed? In otherwords, if the 1st 20 lines have some names = 'offset_name' would I still get 100 rows back or would I get < 100? Also, why is that different then just doing a SET ROWCOUNT = 100 SELECT * FROM #Temp WHERE Name <> 'offset_name' or SELECT TOP 100 * FROM #Temp WHERE Name <> 'offset_name' Thanks, Tom
From: Plamen Ratchev on 22 Apr 2010 18:20 tshad wrote: > I have a SP that I am rewriting and has something like: > ************************************* > Set ROWCOUNT = 100 > > SELECT * > FROM > ( > SELECT TOP 100 PERCENT * > FROM #Temp AS T1 > ) AS Q1 > WHERE Name <> 'offset_name' > ************************************** > > Not sure why he is doing this. Here TOP 100 PERCENT is meaningless. On newer versions of SQL Server the optimizer will ignore it. > > In the derived table he has 280 records. > In the outer table he is doing a SELECT on records that don't have Name = > 'offset_name'. > > #Temp was created with a SELECT/INTO ordered by Name. > > I assume the ordering wouldn't mean anything in this select statement. > Correct, tables have not order. You can get ordered data only using ORDER BY when querying. > Would the ROWCOUNT = 100 take affect after the rows were removed? In > otherwords, if the 1st 20 lines have some names = 'offset_name' would I > still get 100 rows back or would I get < 100? > The query is processed (that means all predicates and logic are applied) and ROWCOUNT limits only the rows from the final result set. > Also, why is that different then just doing a > > SET ROWCOUNT = 100 > > SELECT * > FROM #Temp > WHERE Name <> 'offset_name' > > or > > SELECT TOP 100 * > FROM #Temp > WHERE Name <> 'offset_name' > They are the same, return 100 or less rows in no particular order. Using TOP is a better option because if you do not reset ROWCOUNT all other statements will be affected. -- Plamen Ratchev http://www.SQLStudio.com
From: tshad on 22 Apr 2010 18:50 "Plamen Ratchev" <Plamen(a)SQLStudio.com> wrote in message news:q8WdnQVG7sckVk3WnZ2dnUVZ_qidnZ2d(a)speakeasy.net... > tshad wrote: >> I have a SP that I am rewriting and has something like: >> ************************************* >> Set ROWCOUNT = 100 >> >> SELECT * >> FROM >> ( >> SELECT TOP 100 PERCENT * >> FROM #Temp AS T1 >> ) AS Q1 >> WHERE Name <> 'offset_name' >> ************************************** >> >> Not sure why he is doing this. > > Here TOP 100 PERCENT is meaningless. On newer versions of SQL Server the > optimizer will ignore it. > I thought so - not sure why anyone would ever use 100 PERCENT. >> >> In the derived table he has 280 records. >> In the outer table he is doing a SELECT on records that don't have Name = >> 'offset_name'. >> >> #Temp was created with a SELECT/INTO ordered by Name. >> >> I assume the ordering wouldn't mean anything in this select statement. >> > > Correct, tables have not order. You can get ordered data only using ORDER > BY when querying. > > >> Would the ROWCOUNT = 100 take affect after the rows were removed? In >> otherwords, if the 1st 20 lines have some names = 'offset_name' would I >> still get 100 rows back or would I get < 100? >> > > The query is processed (that means all predicates and logic are applied) > and ROWCOUNT limits only the rows from the final result set. > >> Also, why is that different then just doing a >> >> SET ROWCOUNT = 100 >> >> SELECT * >> FROM #Temp >> WHERE Name <> 'offset_name' >> >> or >> >> SELECT TOP 100 * >> FROM #Temp >> WHERE Name <> 'offset_name' >> > > They are the same, return 100 or less rows in no particular order. Using > TOP is a better option because if you do not reset ROWCOUNT all other > statements will be affected. > So I could use either one in place of the 1st one and get the same result. When you say all other statements are affected, I assume you mean only in this SP. What about in SP called by this procedure or procedures that call this procedure, would the ROWCOUNT stay the same for all of those as well? Thanks, Tom > -- > Plamen Ratchev > http://www.SQLStudio.com
From: Plamen Ratchev on 22 Apr 2010 19:26 tshad wrote: > When you say all other statements are affected, I assume you mean only in > this SP. > Yes, or until you run SET ROWCOUNT 0 to reset it (in the SP I mean). > What about in SP called by this procedure or procedures that call this > procedure, would the ROWCOUNT stay the same for all of those as well? > I will be in effect for SPs you call from inside this procedure but not for SPs calling it. -- Plamen Ratchev http://www.SQLStudio.com
From: tshad on 22 Apr 2010 21:57
"Plamen Ratchev" <Plamen(a)SQLStudio.com> wrote in message news:q8WdnQVG7sckVk3WnZ2dnUVZ_qidnZ2d(a)speakeasy.net... > tshad wrote: >> I have a SP that I am rewriting and has something like: >> ************************************* >> Set ROWCOUNT = 100 >> >> SELECT * >> FROM >> ( >> SELECT TOP 100 PERCENT * >> FROM #Temp AS T1 >> ) AS Q1 >> WHERE Name <> 'offset_name' >> ************************************** >> >> Not sure why he is doing this. > > Here TOP 100 PERCENT is meaningless. On newer versions of SQL Server the > optimizer will ignore it. > >> >> In the derived table he has 280 records. >> In the outer table he is doing a SELECT on records that don't have Name = >> 'offset_name'. >> >> #Temp was created with a SELECT/INTO ordered by Name. >> >> I assume the ordering wouldn't mean anything in this select statement. >> > > Correct, tables have not order. You can get ordered data only using ORDER > BY when querying. But was interesting was that the query did a SELECT/INTO into a Temp table and ordered it: Select a, b into #Temp From Table2 Order by b, a Select * from #Temp This returned the 261 rows in exactly the same order no matter how many times I ran it???? You would have thought the order would have been more random if the ordering didn't really do anything. Thanks, Tom > > >> Would the ROWCOUNT = 100 take affect after the rows were removed? In >> otherwords, if the 1st 20 lines have some names = 'offset_name' would I >> still get 100 rows back or would I get < 100? >> > > The query is processed (that means all predicates and logic are applied) > and ROWCOUNT limits only the rows from the final result set. > >> Also, why is that different then just doing a >> >> SET ROWCOUNT = 100 >> >> SELECT * >> FROM #Temp >> WHERE Name <> 'offset_name' >> >> or >> >> SELECT TOP 100 * >> FROM #Temp >> WHERE Name <> 'offset_name' >> > > They are the same, return 100 or less rows in no particular order. Using > TOP is a better option because if you do not reset ROWCOUNT all other > statements will be affected. > > -- > Plamen Ratchev > http://www.SQLStudio.com |