我们正在从 SQL Server 2008 迁移到 SQL Server 2012,并立即注意到我们所有的表值函数不再以正确排序的顺序提供它们的临时表内容.
We are moving from SQL Server 2008 to SQL Server 2012 and immediately noticed that all our table-valued functions no longer deliver their temp table contents in the correctly sorted order.
代码:
INSERT INTO @Customer SELECT Customer_ID, Name, CASE WHEN Expiry_Date < GETDATE() then 1 WHEN Expired = 1 then 1 ELSE 0 END from Customer **order by Name**在 SQL Server 2008 中,此函数返回按名称排序的客户.在 SQL Server 2012 中,它返回未排序的表.order by" 在 SQL 2012 中被忽略.
In SQL Server 2008 this function returns the customers sorted by Name. In SQL Server 2012 it returns the table unsorted. The "order by" is ignored in SQL 2012.
我们是否必须重新编写所有函数以包含 sort_id,然后在主应用程序中调用它们时对它们进行排序,或者是否有简单的修复方法??
Do we have to re-write all the functions to include a sort_id and then sort them when they are called in the main application or is there an easy fix??
推荐答案你原来的方法有两个问题.
There were two things wrong with your original approach.
在 2012 年,第 1 项的行为似乎发生了变化.现在通常会忽略 SELECT 语句中的 ORDER BY,该语句是插入
In 2012 it looks as though the behaviour has changed with respect to item 1. It now generally ignores the ORDER BY on the SELECT statement that is the source for an INSERT
DECLARE @T TABLE(number int) INSERT INTO @T SELECT number FROM master..spt_values ORDER BY name2008年计划
行为改变的原因是,在以前的版本中,SQL Server 产生了一个在执行之间共享的计划,SET ROWCOUNT 0 (off) 和 SET ROWCOUNT N.排序运算符仅用于确保正确的语义,以防计划由具有非零 ROWCOUNT 集的会话运行.它左侧的 TOP 运算符是 ROWCOUNT TOP.
The reason for the change of behaviour is that in previous versions SQL Server produced one plan that was shared between executions with SET ROWCOUNT 0 (off) and SET ROWCOUNT N. The sort operator was only there to ensure the correct semantics in case the plan was run by a session with a non zero ROWCOUNT set. The TOP operator to the left of it is a ROWCOUNT TOP.
SQL Server 2012 现在为这两种情况生成单独的计划,因此无需将它们添加到计划的 ROWCOUNT 0 版本中.
SQL Server 2012 now produces separate plans for the two cases so there is no need to add these to the ROWCOUNT 0 version of the plan.
如果 SELECT 明确定义了 TOP(除了 TOP 100 PERCENT),但 2012 年的计划中仍可能出现排序这仍然不能保证行的实际插入顺序,在建立 TOP N 之后,计划可能会有另一种排序,例如将行放入聚集索引顺序.
A sort may still appear in the plan in 2012 if the SELECT has an explicit TOP defined (other than TOP 100 PERCENT) but this still doesn't guarantee actual insertion order of rows, the plan might then have another sort after the TOP N is established to get the rows into clustered index order for example.
对于您问题中的示例,如果需要,我将调整调用代码以指定 ORDER BY 名称.
For the example in your question I would just adjust the calling code to specify ORDER BY name if that is what it requires.
关于您的 sort_id 想法来自 SQL Server 中的排序保证 保证在使用 IDENTITY 插入表时,这些分配的顺序将按照 ORDER BY 进行,因此您也可以
Regarding your sort_id idea from Ordering guarantees in SQL Server it is guaranteed when inserting into a table with IDENTITY that the order these are allocated will be as per the ORDER BY so you could also do
DECLARE @Customer TABLE ( Sort_Id INT IDENTITY PRIMARY KEY, Customer_ID INT, Name INT, Expired BIT ) INSERT INTO @Customer SELECT Customer_ID, Name, CASE WHEN Expiry_Date < Getdate() THEN 1 WHEN Expired = 1 THEN 1 ELSE 0 END FROM Customer ORDER BY Name但是您仍然需要在选择查询中按 sort_id 排序,因为没有它就不能保证排序(也许这种 sort_id 方法在这种情况下可能有用其中用于排序的原始列不会被复制到表变量中)
but you would still need to order by the sort_id in your selecting queries as there is no guaranteed ordering without that (perhaps this sort_id approach might be useful in the case where the original columns used for ordering aren't being copied into the table variable)
更多推荐
表值函数
发布评论