Unfortunatly non-inline functions, including table valued ones, run miserably slow in SQL Server. I hope they change it in the future, but for now solutions like yours (and I used similar in the past) just won't deliver performance anywhere near that of a numbers table.

The main issue in your solution (and solutions like it) is that multiple statements are exectuted. Each insert performed within the loop carries so much overhead that just reading a table from a cached page performs tons faster. The set based aproach only has to deal one time with the overhead compared to one time per iteration for the procedural one. On top of it...the return table (called @table in your case) is stored in the temp DB, so data is written to disk (which is slow), compared to a number table, which is just reading data, likely to already be in the cache.

So as things stand:

* When using a function, always try to use a inline table valued function as this is integrated in your query.

* Do not fill up a table one row at a time (Jeff Moden calls it RBAR for good reason)

That said, there are occasions where a non-inline table valued function does better then a inlined one (I recently has such a case). In certain complex queries where the optimiser makes wrong choices this can happen. But you should still fill up the result table in as few statements as possible, which is where a good number table or non-procedural number function comes in.

There are good examples of number/tally functions to be found of this forum. Currently I am still in the process of optimizing one I found and modified. Testing unfotunatly takes time and I want the solution to be scalable across at least several threads, once its finished and if I do succeed, it will end up on this site as well for everyone to use.