Default "Order By"
-
Here is a simple table and 4 simple queries:
create table #table (int1 int, int2 int)
insert into #table (int1,int2) values (1,10)
insert into #table (int1,int2) values (2,9)
insert into #table (int1,int2) values (3,8)
insert into #table (int1,int2) values (4,7)
insert into #table (int1,int2) values (5,6)
insert into #table (int1,int2) values (6,5)
insert into #table (int1,int2) values (7,4)
insert into #table (int1,int2) values (8,3)
insert into #table (int1,int2) values (9,2)
insert into #table (int1,int2) values (10,1)
select * from #table
select int1 from #table
select int2 from #table
select top 1 * from #table
There is no key defined on #table. The four queries do not have an "Order By" clause.
The queries return the data in the order in which the data were entered. Will those four queries always return the same results in the same order? Is there documentation somewhere that specifically states that SQL Server will return rows in the order they were entered? I did not see anything in BOL that mentions the default sort order if none is specified.
What if I add a where clause to select even int1's such as:
select * from #table where int1%2=0
select int1 from #table where int1%2=0
select int2 from #table where int1%2=0
select top 1 * from #table where int1%2=0
Will the server always return data in the order it was entered?
-
If you don't add any indexes then yes. But it's said in the bol that the only garantee to have the order by stay the same is to specify an order by in the select... otherwise the execution plan may change an so does the order of the data presentation.
-
From Books Online:
ORDER BY order_list [ ASC | DESC ]
The ORDER BY clause defines the order in which the rows in the result set are sorted. order_list specifies the result set columns that make up the sort list. The ASC and DESC keywords are used to specify if the rows are sorted in an ascending or descending sequence.
ORDER BY is important because relational theory specifies that the rows in a result set cannot be assumed to have any sequence unless ORDER BY is specified. ORDER BY must be used in any SELECT statement for which the order of the result set rows is important.
-
Thank you for your responses. I will use an order by.
Which version of BOL do you have?
Mine (SQL2K) only shows:
ORDER BY Clause
Specifies the sort for the result set. The ORDER BY clause is invalid in views, inline functions, derived tables, and subqueries, unless TOP is also specified.
Syntax
[ ORDER BY { order_by_expression [ ASC | DESC ] } [ ,...n] ]
Arguments
order_by_expression
Specifies a column on which to sort. A sort column can be specified as a name or column alias (which can be qualified by the table or view name), an expression, or a nonnegative integer representing the position of the name, alias, or expression in select list.
Multiple sort columns can be specified. The sequence of the sort columns in the ORDER BY clause defines the organization of the sorted result set.
The ORDER BY clause can include items not appearing in the select list. However, if SELECT DISTINCT is specified, or if the SELECT statement contains a UNION operator, the sort columns must appear in the select list.
Furthermore, when the SELECT statement includes a UNION operator, the column names or column aliases must be those specified in the first select list.
Note ntext, text, or image columns cannot be used in an ORDER BY clause.
ASC
Specifies that the values in the specified column should be sorted in ascending order, from lowest value to highest value.
DESC
Specifies that the values in the specified column should be sorted in descending order, from highest value to lowest value.
Null values are treated as the lowest possible values.
There is no limit to the number of items in the ORDER BY clause. However, there is a limit of 8,060 bytes for the row size of intermediate worktables needed for sort operations. This limits the total size of columns specified in an ORDER BY clause.
©1988-2000 Microsoft Corporation. All Rights Reserved.
-
I'm on sql2k and its hard to find. Do a serach on SELECT and in the topics listed you should see Parts of a SELECT Statement. Within this topic scroll down to the section on ORDER BY.
good luck - ron
-
For goodness sake, add a Primary Key to the table. Not for sorting but will nearly double the speed at which queries on large wide tables run because SQL doesn't have to work so hard to identify each row uniquely in a given query.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
SQL Server will always try to return the rows in the fastest possible way. This might be the order of the clustered index (if one is present), but with a heap there is no reliable way to predict the result set without an ORDER BY. There might be pages of that table already in memory and SQL Server might choose the return them first and then the rest of that table that need to be fetched into memory. And even if it appears to be always the same order, so is this only implementation-defined. Back in the days of SQL Server 6.5 some operations like GROUP BY (IIRC) always returned a sorted resultset. But this was only due to the algorithm being used. And it broke many code that relied on this, after MS has changed this behaviour in SQL Server 7.0. You really shouldn't rely on the storage engine to work always the same way.
Finally, remember, table are unordered sets and so on...
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
This is not quite true. In addition to what Frank said, there is only one way to guarantee that rows will be returned with a specific order, and that is to say so. That is, if you do not tell the server to do something, then you cannot expect it to do it either, right?
If you do not say ORDER BY, then the server will always return the rows in the order it finds the rows.
This may very well for some situations appear to be in the intended order, but there are no guarantees. (unless you say ORDER BY)
/Kenneth
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply