保证表值函数结果的顺序

本文介绍了保证表值函数结果的顺序的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

PREMISE ：应用程序代码无法更改。条件非常具体。我正在寻找书籍以外的东西，如果可以的话，这是一种最后的解决方法。

我有一个表值函数（内联）和7条记录。有时它可能只有1或15（但很少）。

该函数仅由应用程序以这种方式使用，没有任何ORDER BY。 p>

select * from dbo.myfunction（...）

在您的经验中，根据您的经验，有什么方法可以确保（就您所观察到的使用特定技术而言）确保结果是返回由第二列排序？列是：varchar（3），datetime，varchar（50）。 不要让我开始使用 select * ，它是

你的想法？我宁愿将理论放在讨论之外。如果你能坚持实践经验并在家里保持最佳做法，我也会很感激。 >更新 这就是现在的样子

create function dbo.myfunction（... 。）返回@RES表（ [＃] int identity主键聚簇， [Varchar3Col] varchar（3）， [DateTimeCol] datetime， [Varchar50Col] varchar（50））as BEGIN declare @ RES2表（ rn int， [Varchar3Col] varchar 3）， [DateTimeCol] datetime， [Varchar50Col] varchar（50）） insert @ RES2 select rn = row_number（）over （... by action_time）， [Varchar3Col] [DateTimeCol] [Varchar50Col] from（.....） inner join（.... 。）ON（.....） declare @i int set @i = 0 while @@ rowcount> 0开始 set @ i = @ i + 1 插入@RES 从@ RES2 中选择[Varchar3Col]，[DateTimeCol]，[Varchar50Col] 其中rn = @ i end return END GO

• 如果您查看以上内容，则@RES的人口按照所需的顺序手动完成。
• @RES具有代表订单已插入。
• 这些列足够小，以至于20行应始终适合单个8K页面

这会工作吗？（从应用程序层直接选择SELECT）？

解决方案

。不仅如此，内联TVF甚至可能会返回超过您认为应该的行数，并且行将在执行TVF后进行修剪（基本上是TVF定义中的谓词）可以从TVF中拉出并移动到查询树中的其他地方）。请参阅 T -SQL函数并不意味着一定的执行顺序，例如发生这种情况。

将内联TVF转换为多语句，将引入一些过程顺序，因为这些语句不能按顺序执行，但是TVF结果可能会被重新排序，排序，拆分，假脱机，基本上被优化程序生成的计划所破坏，并最终打破您对输出顺序的假设。我很害怕如果你必须有一定的执行顺序，游标是你最好的朋友。

PREMISE: The application code cannot be changed. The conditions are very specific. I am looking for something off the books, a last resort workaround if you may.

I have a table-valued function (inline) that produces between 2 and 7 records. At times it could be only 1 or up to 15 (but rarely).

The function is only used by an application in this way, without any ORDER BY.

select * from dbo.myfunction(...)

Is there any way at all, in your experience, to guarantee ensure (as far as you have ever observed using a particular technique) that the results are returned ordered by the second column? Columns are: varchar(3), datetime, varchar(50). Don't get me started on select *, it is INTENTIONAL so that the front end will display however many columns I make the function display in the future.

From experience, with a single index (clustered PK) to traverse the data, any current version of SQL Server and SP level should always perform a simple INDEX SCAN on <20 records without parallelism, thereby giving me ordered results in the application select.

Your thoughts? I would prefer to keep theory out of the discussion. If you can stick to practical experience and keep sermons about best practice at home, I would also appreciate it.

UPDATED This is what it looks like now

create function dbo.myfunction(....) returns @RES table ( [#] int identity primary key clustered, [Varchar3Col] varchar(3), [DateTimeCol] datetime, [Varchar50Col] varchar(50) ) as BEGIN declare @RES2 table ( rn int, [Varchar3Col] varchar(3), [DateTimeCol] datetime, [Varchar50Col] varchar(50) ) insert @RES2 select rn=row_number() over (order by action_time), [Varchar3Col] [DateTimeCol] [Varchar50Col] from (.....) inner join (.....) ON (.....) declare @i int set @i = 0 while @@rowcount > 0 begin set @i=@i+1 insert @RES select [Varchar3Col], [DateTimeCol], [Varchar50Col] from @RES2 where rn=@i end return END GO

• If you look at the above, the population of @RES is done sequentially in the order desired, manually.
• @RES has a clustered PK representing the order inserted.
• the columns are small enough that 20 rows should always fit in a single 8K page

Would this work (with the straightforward SELECT from the application layer)?

解决方案

For an inline TVF nothing will really work. Not only that, the inline TVF may even return more rows than you believe it should, and the rows will be trimmed after the TVF executed (basically a predicate in the TVF definition can be pulled out of the TVF and moved somewhere else in the query tree). See T-SQL functions do no imply a certain order of execution for an example of this happening.

Converting the inline TVF to a multi statement one will introduce some procedural order, since the statements cannot be executed out of order, but the TVF result may be re-ordered, sorted, split, spooled, basically mangled by the optimizer generated plan and in the end break your assumption about output order. I'm afraid if you must have a certain order of execution, cursors are your best friend.