is the Top or Order by done first?
in [Programming SQL]
|
From: Tom Cooper on 23 Apr 2010 02:00 "tshad" <tfs(a)dslextreme.com> wrote in message news:%23$9Xcho4KHA.1444(a)TK2MSFTNGP06.phx.gbl... > <snip> >> 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 <snip> That is a very common experience. But that does not mean it will do it the next time. No matter how many times you've run the test. Even if SQL appears to consistantly return the rows in the same order without an ORDER BY in your select statement thru many, many trials, you cannot depend on that behavior. If you want the rows returned in a particular order ALWAYS use an order by in the select. If you don't, there is a very good chance that at some time in the future, your rows will come back in some different order. Among the things which can change the order if you don't use an order by are 1) installing a new release, service pack, cumulative update, or patch to SQL Server, 2) moving the database to a different server, 3) changing the disk subsystem the database is running on, 4) changing options like max degree of parallelism, 4) creating or dropping an index, 5) the number of rows in the table increases, 6) how busy the server is, and 7) someone else is selecting the data from the table at the same time as you are. There may be more reasons. In those cases where you don't care what order the rows are returned in, then don't use an order by. But if you care, always use an order by. Tom
From: tshad on 23 Apr 2010 02:55
"Tom Cooper" <tomcooper(a)comcast.net> wrote in message news:eXMxVpq4KHA.1444(a)TK2MSFTNGP06.phx.gbl... > > "tshad" <tfs(a)dslextreme.com> wrote in message > news:%23$9Xcho4KHA.1444(a)TK2MSFTNGP06.phx.gbl... >> > <snip> >>> 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 > <snip> > > That is a very common experience. But that does not mean it will do it > the next time. No matter how many times you've run the test. Even if SQL > appears to consistantly return the rows in the same order without an ORDER > BY in your select statement thru many, many trials, you cannot depend on > that behavior. If you want the rows returned in a particular order ALWAYS > use an order by in the select. If you don't, there is a very good chance > that at some time in the future, your rows will come back in some > different order. Among the things which can change the order if you don't > use an order by are 1) installing a new release, service pack, cumulative > update, or patch to SQL Server, 2) moving the database to a different > server, 3) changing the disk subsystem the database is running on, 4) > changing options like max degree of parallelism, 4) creating or dropping > an index, 5) the number of rows in the table increases, 6) how busy the > server is, and 7) someone else is selecting the data from the table at the > same time as you are. There may be more reasons. > > In those cases where you don't care what order the rows are returned in, > then don't use an order by. But if you care, always use an order by. I agree. My question was because an ORDER BY was being used to fill a temp table and then selecting on that temp table without any order by, why was it ordered correctly. The first SELECT that was putting the data into the temp table, had 2 views it was joining and each view had about 4 joins with about 4 where clauses. What I was doing was getting rid of the temp table altogether and just doing the original SELECT with the ORDER BY and getting the exact results. Our concern was that the results be the same and apparently, it was working correctly since there had been no complaints from the various companies that used the page that called this program. I assume the order was correct because this routine was used to bring back a tree so the order would have to have been correct. My changes should actually make the responses more consistant since the results are coming directly from the ORDER BY where as the old one wasn't. But I couldn't guarentee that the results were correct before and that is my problem and what my client is going to be asking. Thanks, Tom > > Tom > > |