"SQL" shared table-valued function fmakerows for generating rows

function: pass in an integer x, return an X row of table, only one column rowno, store each row ordinal.

For this demand, I first find there is no ready-made function or process, the result is not found, if passing friends know, also hope to inform, thank you.

Examples of Use:

As to which scenes the function can be applied to, imaginative achievement here, the person who needs to feel useful naturally, feel useless explanation not need. On the code:

/*----------------------function: Generate row 0.01author:ahdungupdate:201412310925----------------------*/CREATE FUNCTIONDbo. Fmakerows (@num INT)RETURNS @t TABLE(RowNoINT)BEGINIF @num  is NULL OR @num <= 0 RETURNINSERT @t VALUES(1)DECLARE @no INT = 0 while POWER(2,@no+1)<=@numBEGININSERT @t SELECTRowNo+POWER(2,@no) from @tSET @no += 1ENDIF @num <> POWER(2,@no)BEGINDECLARE @pow INT = POWER(2,@no)INSERT @t SELECT TOP(@num-@pow) RowNo+@pow  from @tENDRETURNEND

Implementation Description: the principle is to give @t plug an initial line, the loop insert itself, so 1 to 2, 2 to 4, 4 to change everything .... The number of rows @t after each lap is twice times the previous lap, until the number of rows X2 is greater than the desired number of rows (@num). That is, to control the number of rows in the range less than or equal to @num, and finally to extract a portion of the existing rows to complement the bad rows. For example, the number of rows required is 13, go to 3 laps, @t has 8 lines, it will stop, because then turn into 16 lines, 8 distance 13 difference 5 lines finally by extracting top 5 from @t.

The realization of the function at the beginning of the thought is based on the @num loop, a row per lap, a few lines to turn a few laps (progressive method), logic is very simple, but this is honest, the fact proved less efficient than the above method (number of rows multiplied), the two methods tested to 500 lines there is a significant difference, to 16384 lines, Multiplication method around 140ms, step by step 400ms, I think the reason is that the multiplication method greatly reduced the number of cycles, 16384 lines only 14 laps, and the line method to honestly turn to 16384 laps now ~ fear.

Also want to get a row from a system table/view that must exist, such as sys.objects, but this will access the underlying table, even if you do not select any of its fields, so performance is necessarily not as good as pure memory operation, try not to try. It is also not true, as a function, the less dependent the more robust.

Passing friends if there is a better way, also please advise, thank you very much.

"SQL" shared table-valued function fmakerows for generating rows