How to generate a range of numbers between two numbers?

by

Select non-persisted values with the VALUES keyword. Then use JOINs to generate lots and lots of combinations (can be extended to create hundreds of thousands of rows and beyond).

Short and fast version (not that easy to read):

WITH x AS (SELECT n FROM (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) v(n))
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM x ones, x tens, x hundreds, x thousands
ORDER BY 1

Demo

More verbose version:

SELECT ones.n + 10*tens.n + 100*hundreds.n + 1000*thousands.n
FROM (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) ones(n),
     (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) tens(n),
     (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) hundreds(n),
     (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) thousands(n)
ORDER BY 1

Demo

Both versions can easily be extended with a WHERE clause, limiting the output of numbers to a user-specified range. If you want to reuse it, you can define a table-valued function for it.

More Related Contents:

  1. I dont know sql syntax for my table [closed]
  2. Is it possible to write single query on two table which are not connected to each other?
  3. T-SQL split string
  4. Can I Comma Delimit Multiple Rows Into One Column? [duplicate]
  5. Conditional WHERE clause in SQL Server
  6. Multiple INSERT statements vs. single INSERT with multiple VALUES
  7. Split function equivalent in T-SQL?
  8. Is the NOLOCK (Sql Server hint) bad practice?
  9. Multi-statement Table Valued Function vs Inline Table Valued Function
  10. Is having an ‘OR’ in an INNER JOIN condition a bad idea?
  11. Where value in column containing comma delimited values
  12. Parse comma-separated string to make IN List of strings in the Where clause
  13. SQL query to split column data into rows
  14. Common Table Expression, why semicolon?
  15. T-SQL split string based on delimiter
  16. Bulk DELETE on SQL Server 2008 (Is there anything like Bulk Copy (bcp) for delete data?)
  17. Compare DATETIME and DATE ignoring time portion
  18. Split function in SQL Server 2008
  19. Cannot use a CONTAINS or FREETEXT predicate on table or indexed view because it is not full-text indexed
  20. SQL “between” not inclusive
  21. Can I set ignore_dup_key on for a primary key?
  22. Using PIVOT in SQL Server 2008
  23. Stored procedure that exports data into csv files only exports to one file
  24. How do I prevent a database trigger from recursing?
  25. How can I group by date time column without taking time into consideration
  26. How to combine results of two queries into a single dataset
  27. When using GETDATE() in many places, is it better to use a variable?
  28. Executing SQL Server Agent Job from a stored procedure and returning job result
  29. Cumulative Total in MS Sql server [duplicate]
  30. COALESCE Function in TSQL

Leave a Comment