- If you have an ORDER BY in the top-most SELECT block in a query, the presentation order of the results honor that ORDER BY request
- If you have a TOP in the same SELECT block as an ORDER BY, any TOP computation is performed with respect to that ORDER BY. For example, if there is a TOP 5 and ORDER BY clause then SQL Server picks the TOP 5 rows within a given sort. Note that this does not guarantee that subsequent operations will somehow retain the sort order of a previous operation. The query optimizer re-orders operations to find more efficient query plans
- Cursors over queries containing ORDER BY in the top-most scope will navigate in that order
- INSERT queries that use SELECT with ORDER BY to populate rows guarantees how identity values are computed but not the order in which the rows are inserted
- SQL Server 2005 supports a number of new "sequence functions" like RANK(), ROW_NUMBER() that can be performed in a given order using a OVER clause with ORDER BY
- For backwards compatibility reasons, SQL Server provides support for assignments of type SELECT @p = @p + 1 ... ORDER BY at the top-most scope.
[Name] VARCHAR(100),
Age INT,
[Address] VARCHAR(500)
)
INSERT INTO @tmpPerTable
SELECT *
FROM
(
SELECT 'Jim' AS 'Name',15 AS 'Age','Road 1' AS 'Address'
UNION ALL
SELECT 'Tim' AS 'Name',15 AS 'Age','Road 2' AS 'Address'
UNION ALL
SELECT 'Sim' AS 'Name',12 AS 'Age','Road 2' AS 'Address'
UNION ALL
SELECT 'Sam' AS 'Name',11 AS 'Age','Road 4' AS 'Address'
UNION ALL
SELECT 'Jack' AS 'Name',9 AS 'Age','Road 1' AS 'Address'
UNION ALL
SELECT 'Tom' AS 'Name',16 AS 'Age','Road 3' AS 'Address'
) a
ORDER BY a.[Address],a.Age
SELECT * FROM @tmpPerTable
Name Age Address
---------- ----------- ----------
Jim 15 Road 1
Tim 15 Road 2
Sim 12 Road 2
Sam 11 Road 4
Jack 9 Road 1
Tom 16 Road 3
(6 行受影响)
[Name] VARCHAR(10),
Age INT,
[Address] VARCHAR(10)
)
INSERT INTO @tmpPerTable
SELECT TOP 100 PERCENT *
FROM
(
SELECT 'Jim' AS 'Name',15 AS 'Age','Road 1' AS 'Address'
UNION ALL
SELECT 'Tim' AS 'Name',15 AS 'Age','Road 2' AS 'Address'
UNION ALL
SELECT 'Sim' AS 'Name',12 AS 'Age','Road 2' AS 'Address'
UNION ALL
SELECT 'Sam' AS 'Name',11 AS 'Age','Road 4' AS 'Address'
UNION ALL
SELECT 'Jack' AS 'Name',9 AS 'Age','Road 1' AS 'Address'
UNION ALL
SELECT 'Tom' AS 'Name',16 AS 'Age','Road 3' AS 'Address'
) a
ORDER BY a.[Address],a.Age
SELECT * FROM @tmpPerTable
Name Age Address
---------- ----------- ----------
Jim 15 Road 1
Tim 15 Road 2
Sim 12 Road 2
Sam 11 Road 4
Jack 9 Road 1
Tom 16 Road 3
(6 行受影响)
[Name] VARCHAR(10),
Age INT,
[Address] VARCHAR(10)
)
INSERT INTO @tmpPerTable
SELECT TOP 100 *
FROM
(
SELECT 'Jim' AS 'Name',15 AS 'Age','Road 1' AS 'Address'
UNION ALL
SELECT 'Tim' AS 'Name',15 AS 'Age','Road 2' AS 'Address'
UNION ALL
SELECT 'Sim' AS 'Name',12 AS 'Age','Road 2' AS 'Address'
UNION ALL
SELECT 'Sam' AS 'Name',11 AS 'Age','Road 4' AS 'Address'
UNION ALL
SELECT 'Jack' AS 'Name',9 AS 'Age','Road 1' AS 'Address'
UNION ALL
SELECT 'Tom' AS 'Name',16 AS 'Age','Road 3' AS 'Address'
) a
ORDER BY a.[Address],a.Age
SELECT * FROM @tmpPerTable
Name Age Address
---------- ----------- ----------
Jack 9 Road 1
Jim 15 Road 1
Sim 12 Road 2
Tim 15 Road 2
Tom 16 Road 3
Sam 11 Road 4
(6 行受影响)
seq INT IDENTITY(1,1), --这里不得不多定义一列
[Name] VARCHAR(10),
Age INT,
[Address] VARCHAR(10)
)
Set Identity_insert ON;
INSERT INTO @tmpPerTable
SELECT ROW_NUMBER() OVER (ORDER BY a.[Address],a.Age), *
FROM
(
SELECT 'Jim' AS 'Name',15 AS 'Age','Road 1' AS 'Address'
UNION ALL
SELECT 'Tim' AS 'Name',15 AS 'Age','Road 2' AS 'Address'
UNION ALL
SELECT 'Sim' AS 'Name',12 AS 'Age','Road 2' AS 'Address'
UNION ALL
SELECT 'Sam' AS 'Name',11 AS 'Age','Road 4' AS 'Address'
UNION ALL
SELECT 'Jack' AS 'Name',9 AS 'Age','Road 1' AS 'Address'
UNION ALL
SELECT 'Tom' AS 'Name',16 AS 'Age','Road 3' AS 'Address'
) a
SELECT * FROM @tmpPerTable
seq Name Age Address
----------- ---------- ----------- ----------
1 Jack 9 Road 1
2 Jim 15 Road 1
3 Sim 12 Road 2
4 Tim 15 Road 2
5 Tom 16 Road 3
6 Sam 11 Road 4
(6 行受影响)
The order of a SQL Select statement without Order By clause
No, that behavior cannot be relied on. The order is determined by the way the query planner has decided to build up the result set. simple queries like select * from foo_table
are likely to be returned in the order they are stored on disk, which may be in primary key order or the order they were created, or some other random order. more complex queries, such as select * from foo where bar < 10
may instead be returned in order of a different column, based on an index read, or by the table order, for a table scan. even more elaborate queries, with multipe where
conditions, group by
clauses, union
s, will be in whatever order the planner decides is most efficient to generate.
The order could even change between two identical queries just because of data that has changed between those queries. a "where" clause may be satisfied with an index scan in one query, but later inserts could make that condition less selective, and the planner could decide to perform a subsequent query using a table scan.
2、 http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=186664