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 rowssys.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
CREATE FUNCTION dbo.FMakeRows(@num INT)
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)
WITH cte AS (
SELECT 1 AS 'RowNo',1 AS 'Lv'
SELECT RowNo+Lv,Lv*2 FROM cte WHERE RowNo+Lv<=@num
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.