【SQL】分享表值函数FMakeRows，用于生成行

------------更新：201501071730------------

评论中又有一位【笑东风】兄给出改善建议，在此先感谢他。原理是借助行数较多的一个系统视图sys.all_columns与自身做cross join，以得到大量现成行数，详情请见回复。在我的原文中我也提到考虑过这种借助现有系统对象得到行的方法，但我想当然认为这样会导致访问基础表，性能不会好，所以试都没试就pass了，但事实证明我错了，他的法子经测性能比倍增法好太多，再次自我教训，实践才是硬道理！！！再次感谢【笑东风】兄。最终实现如下：

/*----------------------函数：生成行 0.01Author:AhDungUpdate:201412310925----------------------*/CREATE FUNCTION dbo.FMakeRows(@num INT)RETURNS TABLERETURN (SELECT TOP (CASE WHEN @num IS NULL OR @num<0 THEN 0 ELSE @num END) ROW_NUMBER() OVER(ORDER BY a.object_id) AS 'RowNo'FROM sys.all_columns a CROSS JOIN sys.all_columns b)

------------更新：201501061241------------

评论中有朋友提到不如用with，我理解他说的是CTE递归，在此先感谢一下这位【空紫竹】兄提出建议。遂试了下用CTE实现，代码如下：

CREATE FUNCTION dbo.FMakeRows2(@num INT)RETURNS TABLERETURN (WITH cte AS (SELECT 1 AS 'RowNo',1 AS 'Lv'UNION ALLSELECT RowNo+Lv,Lv*2 FROM cte WHERE RowNo+Lv<=@numUNION ALLSELECT RowNo+Lv*2,Lv*2 FROM cte WHERE RowNo+Lv*2<=@num)SELECT RowNo FROM cte)

功能一样，原理是递归倍增，语句变少了，但性能比不上原文的方法，16384行上述方法要400ms左右，与逐行法差不多，而原文方法只要140ms，所以算不上好方法，权当学习一下CTE递归知识。如果我的实现有问题，还望路过大侠指点，谢谢。

------------原文：201412311300------------

作用：传入整数x，返回一张x行的表，只有一列RowNo，存储各行序号。

对于这个需求，我先是找有没有现成的函数或过程，结果是没找到，如果路过的朋友知道，还望告知，谢谢。

使用示例：

至于该函数具体可以应用到哪些场景，只可意会，需要的人自然觉得有用，觉得没用的说明不需要。上代码：

/*----------------------函数：生成行 0.01Author:AhDungUpdate:201412310925----------------------*/ALTER FUNCTION dbo.FMakeRows(@num INT)RETURNS @t TABLE (RowNo INT)BEGINIF @num IS NULL OR @num <= 0 RETURNINSERT @t VALUES(1)DECLARE @no INT = 1WHILE @no*2 <= @numBEGININSERT @t SELECT RowNo+@no FROM @tSET @no *= 2ENDINSERT @t SELECT TOP (@num-@no) RowNo+@no FROM @tRETURNEND

实现说明：原理是先给@t塞一个初始行，完了循环insert自身，如此1变2、2变4、4变万物……，每一圈后@t的行数都是上一圈的2倍，直到行数x2大于所需行数（@num）前打住，即要把行数控制在小于等于@num的范围内，最后从现有行中抽取一部分补齐所差的行。例如，需要的行数是13，转到3圈后，@t有8行，就要打住了，因为再转就成16行了，8距离13所差的5行最后通过从@t中抽取top 5补齐。

实现该函数一开始想到的是根据@num循环，每圈插一行，需要几行就转几圈（逐行法），逻辑很简单，但这样做很老实，事实证明效率也不如上述方法（行数倍增），两种方法经测试到500行时就有明显差异了，到16384行时，倍增法在140ms左右，逐行法400ms左右，我想原因就是倍增法大大减少了循环圈数，16384行只需转14圈，而逐行法要老老实实转足16384圈呐~怕怕。

也想过从某个必定存在的系统表/视图获取行，如sys.objects，但这样会访问基础表，即使你根本不select它的任何字段，这样性能必然不如纯内存操作来的好，试都不用试。再说也不地道，作为函数，依赖越少越健壮。

路过朋友如有更好方法，还请不吝赐教，非常感谢。