Ordering guarantees in SQL Server...(SQLServer中保证排序不被优化,insert into ....select...order by时插入顺序不对)
来源:互联网 发布:jquery向数组添加元素 编辑:程序博客网 时间:2024/05/21 05:38
- 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=186664The behavior of the IDENTITY function when used with SELECT INTO or INSERT .. SELECT queries that contain an ORDER BY clause
max degree of parallelism Option
- Ordering guarantees in SQL Server...(SQLServer中保证排序不被优化,insert into ....select...order by时插入顺序不对)
- SQL SERVER Insert into Select
- insert into select 插入不进去数据
- SQL Server 和 MySQL中 from where group by having order select 执行顺序
- SQL插入查询结果 insert into select
- 1、SQL Server:SELECT INTO 和 INSERT INTO SELECT
- SQL Server中使用order by charindex按指定顺序排序
- SQL中SELECT INTO和INSERT INTO SELECT语句介绍
- SQL中SELECT INTO和INSERT INTO SELECT语句介绍
- SQL中SELECT INTO和INSERT INTO SELECT(复制表)
- SQL中SELECT INTO和INSERT INTO SELECT语句介绍
- SQL中SELECT INTO和INSERT INTO SELECT语句
- 如何优化用SQL语句INSERT INTO … SELECT插入数据时锁全表的问题
- SELECT IN ORDER BY CHARINDEX (排序)
- SQL INSERT INTO SELECT
- sql语句中insert into 表中 select多个值后插入数据
- MYSQL INSERT INTO SELECT 不插入重复数据
- MYSQL INSERT INTO SELECT 不插入重复数据
- [[dic objectForKey:@"name"] description]
- 图形处理(二)固定边界参数化
- 邮件系统安装配置(postfix + dovecot)
- ruby 基础知识点
- 在Android Studio 和 Eclipse 的 git 插件操作 "代码提交"以及"代码冲突"
- Ordering guarantees in SQL Server...(SQLServer中保证排序不被优化,insert into ....select...order by时插入顺序不对)
- iOS开发 : 坐标变换Quartz 2D中的CGContextTranslateCTM、CGContextScaleCTM
- 常用gnuplot脚本
- linux 4位权限详解
- 学习记录20160125
- 图形处理(三)简单拉普拉斯网格变形-Siggraph 2004
- elasticsearch2.1.1 安装
- VS2008下,编译.asm
- GIF图片使用