存储过程未按预期顺序返回行 [英] Stored procedure not returning rows in expected order

问题描述

我有一个存储过程，它运行查询并将表结果保存在一个单独的表中.查询在存储过程中定义如下:

I have a stored Procedure that runs a query and saves the table result in a separate table.The query is defined in storedProcedure as follow:

BEGIN
SET NOCOUNT ON;
DECLARE @Command nvarchar(MAX) = N'', @Condition nvarchar(4000) = N'';
SET @Command = @Command + N'
SELECT * FROM(
  SELECT ROW_NUMBER() OVER(ORDER BY No,Account.AccCode) AS RowNo, 
     ParentAccount.AccCode AS ParentAccCode, ParentAccount.Description AS 
     ParentAccountDescription, Account.AccCode, Account.Description AS 
     AccountDescription, 
     Detail1.DetailCode AS Detail1Code, Detail1.Description AS 
     Detail1Description,AccDoc.No AS No,Account.AccCode
   FROM AccDoc 
     INNER JOIN AccDocDetail AS DocDetail ON AccDoc.Guid = 
     DocDetail.DocGuid
     LEFT JOIN Account ON Account.Guid = DocDetail.AccountGuid
     LEFT JOIN Account AS ParentAccount ON ParentAccount.Guid = Account.ParentGuid
     LEFT JOIN AccDetail AS Detail1 ON DocDetail.Detail1Guid=Detail1.Guid
      WHERE (AccDoc.Company = '391891BA-DCC9-4B76-9588-9C97E0C2071A') AND 
          (AccDoc.Branch = 'FDFCD806-787F-4BB9-9E3F-6E5A57D409E6') AND (AccDoc.Period = 'DA2C20CC-1132-4008-853E-E4FE6D85EF3A')
   ORDER BY No,AccCode'

PRINT @Command;

现在我定义一个表变量并将结果保存到这个:

Now I define a table variable and save the result of that to this:

    DECLARE @tempTable Table(RowNo int, ParentAccCode nvarchar(15),...) 
    INSERT INTO @tempTable
    EXECUTE sp_executesql @Command, N'@Company uniqueidentifier, @Branch uniqueidentifier, @Period uniqueidentifier, @StartTurning nvarchar(20), @EndTurning nvarchar(20),@StartDocumentNo nvarchar(20), @EndDocumentNo nvarchar(20),   @StartDate nvarchar(8), @EndDate nvarchar(8)',
        @Company = @Company, @Branch = @Branch, @Period = @Period, @StartTurning = @StartTurning, @EndTurning = @EndTurning, @StartDocumentNo = @StartDocumentNo, @EndDocumentNo = @EndDocumentNo, @StartDate = @StartDate, @EndDate = @EndDate;

最后我在@tempTable 上运行一个查询:

And in the end I run a query on @tempTable:

SELECT * FROM @tempTable
END
GO

查询结果保存在 tempTable 中..但是表的排序不是真的.当我单独运行保存在@Command 变量中的查询时，它的排序是真的:

Result of query is saved in tempTable..But sort of table is not true.When I run query that saved in @Command variable separately,It's sort is true:

ORDER BY No,AccCode

但是当将其保存在单独的变量(@tempTable)中并在其上选择时，记录的记录被破坏了..!!

But when save that in a separate variable(@tempTable) and select on it,The records of the records are broken..!!

这是什么原因?我能做什么?

What's that reason? What can I do?

推荐答案

唯一在磁盘上对数据进行排序的方式或时间基于 集群索引.检索数据时，必须指定ORDER BY，否则无法保证结果.

The only way or time data is sorted on disk is based on the CLUSTERED INDEX. When you retrieve data, you must specify an ORDER BY or the results are not guaranteed.

• Connor Cunninghan 没有安全带期待没有 ORDER BY 的订单.
• Aaron Bertrand 展示了另一个例子

因此，您必须使用:

SELECT * 
FROM @tempTable
ORDER BY No, AccCode

不过，我不明白您为什么要将这些数据转储到表变量中.它们的表现通常比 差临时表，因为它们的基数估计值(无统计数据)很差，并且您的 CRUD 操作是连载.

Though, I don't see why you are dumping this data into a table variable. They usually perform worse than temp tables because they have poor cardinality estimates (no statistics) and your CRUD operations are serialized.

这篇关于存储过程未按预期顺序返回行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！