[SQL] Share the table value function FMakeRows for generating rows

** ------------ updated: 201501071730 ------------**

Another [Laughing East] brother in the comments gave suggestions for improvement, so I'd like to thank him in advance. The principle is that with the help of a system view with a high number of rows**sys.all_columns** Do cross join with itself to get a large number of ready rows, see reply for details. In my original article I also mentioned considering this method of getting rows with the help of existing system objects, but I thought for sure that this would lead to accessing the base table and the performance would not be good, so I passed without even trying, but it turns out I was wrong, his method was tested to have a much better performance than the multiplication method, once again self-lesson, practice is hard!!! Thanks again to brother [Laughing East]. The end result is as follows.

/*---------------------- function： generate lines 0.01 Author:AhDung Update:201412310925 ----------------------*/ CREATE FUNCTION dbo.FMakeRows(@num INT) RETURNS TABLE RETURN ( 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 )

** ------------ updated: 201501061241 ------------**

A friend in the comments mentioned that it would be better to use WITH, and I understood him to be talking about CTE recursion, so I'd like to thank this [empty purple bamboo] brother in advance for suggesting it. So I tried to implement it with CTE and the code is as follows.

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

The function is the same, the principle of recursive multiplication, the statement becomes less, but the performance is not as good as the original method, 16384 lines of the above method to about 400ms, and line by line method, while the original method as long as 140ms, so it is not a good method, right to learn a little CTE recursive knowledge. If I have problems with my implementation, I'd appreciate any pointers from passing gurus, thanks.

** ------------ original: 201412311300 ------------**

** Role.** Pass in the integer x to return a table of x rows, with only one RowNo column, storing the serial numbers of the rows.

* For this requirement, I first looked to see if there was a readily available function or procedure, but as it turned out, I couldn't find one, so if anyone passing by knows, please let me know, thanks.*

** Example of use.**

As for thefunction What specific scenarios can be applied to， can only be understood， Those who need it will naturally find it useful， If you don't think it's useful, you don't need it.。 up-code：

/*---------------------- function： generate lines 0.01 Author:AhDung Update:201412310925 ----------------------*/ ALTER FUNCTION dbo.FMakeRows(@num INT) RETURNS @t TABLE (RowNo INT) BEGIN IF @num IS NULL OR @num <= 0 RETURN INSERT @t VALUES(1) DECLARE @no INT = 1 WHILE @no*2 <= @num BEGIN INSERT @t SELECT RowNo+@no FROM @t SET @no *= 2 END INSERT @t SELECT TOP (@num-@no) RowNo+@no FROM @t RETURN END

** Description of implementation.** The principle is to first give @t stuff an initial line, finished loop INSERT itself, so 1 to 2, 2 to 4, 4 to everything ......, after each circle @t the number of lines are 2 times the last circle, until the number of lines x2 is greater than the number of lines required (@num) before stopping, that is, to control the number of lines in the range of less than or equal to @num, and finally take a part of the existing lines to make up the difference. For example, the number of rows needed is 13, and after 3 turns, @t has 8 rows, so you have to stop, because another turn will make 16 rows, and the 5 rows that 8 is short of 13 is finally made up by taking the top 5 from @t.

The realization of the function at first thought is based on @num loop, insert a line per circle, need a few lines on a few turns (line by line method), the logic is very simple, but this is very honest, it turns out that the efficiency is not as good as the above method (line multiplier), the two methods tested to 500 lines there is a significant difference to 16384 lines, multiplier method in about 140ms, line by line method 400ms or so, I think the reason is that the multiplier method greatly reduces the number of loops, 16384 lines only need to turn 14 turns, and line by line method to honestly turn the full 16384 turns na ~ afraid.

I've also thought about the idea of taking a table from some system that must exist/ View Get Row， as ifsys.objects， But this will access the base table， Even if you don't evenselect Any of its fields， This is necessarily not as good as pure memory operations， You don't even have to try.。 Besides, it's not nice.， act asfunction， The less dependence, the more robust。

** If you have a better way to do this, please do not hesitate to ask, thank you very much.**