1

Is it considered a good practice to use the undocumented spt_values table in SQL Server as a source of consecutive numbers?

Is there a risk that this could break in a future release of SQL Server or it safe as long as you don't care about portability?

I have been burned by using undocumented functions in Oracle before (wm_concat specifically) so my specific question is the likelihood that spt_values will continue to be supported in future SQL Server versions.

wrschneider
  • 17,913
  • 16
  • 96
  • 176
  • 2
    It is not docummented and could break your code after any upgrade. In production you should avoid it. – Lukasz Szozda Mar 05 '16 at 16:08
  • The next thing is the number of rows in this table, if in future someone deletes some entries you will get smaller range (if you do not use multiple `CROSS JOIN`). Today it is `2523` what if it will be only `100`? – Lukasz Szozda Mar 05 '16 at 16:19
  • 1
    [Add a built-in table of numbers](https://connect.microsoft.com/SQLServer/Feedback/Details/258733) still open :) – Lukasz Szozda Mar 05 '16 at 16:24
  • Several good answers on what to choose to make tally tables [here](http://stackoverflow.com/q/1393951/243373) – TT. Mar 05 '16 at 17:08

0 Answers0