Generating a number sequence in SQL

There are many reasons you might need a number sequence in SQL. I’m going to show you how to efficiently generate such a sequence in a set-based manner, all in memory, and without using any loops. In future articles I’ll get into some uses for such a sequence, but for now, let’s open up SSMS and just roll with it.

A Loopy Solution

If you are a typical imperative programmer, you’re probably used to thinking of number sequences in terms of loops. Say that we wanted to generate a sequence of 100,000 numbers. A naive first attempt in T-SQL using a loop might look like Continue Reading →