Dynamic Number Generation for T-SQL

Yes, I know what you might be thinking, this is an XNA game development blog and I am adding some T-SQL code. But this code, is very funky, so much so that is the name of the file I have.

I have never needed code like this until recently and have decided to throw it out there for the world to use.  What this code does is generate, as many numbers as you like and numbers them from 1 to, however many you want.


WITH
 L0     AS(SELECT 1 as c UNION ALL SELECT 1),
 L1     AS(SELECT 1 as c FROM L0 AS A CROSS JOIN L0 AS B),
 L2     AS(SELECT 1 as c FROM L1 AS A CROSS JOIN L1 AS B),
 L3     AS(SELECT 1 as c FROM L2 AS A CROSS JOIN L2 AS B),
 L4     AS(SELECT 1 as c FROM L3 AS A CROSS JOIN L3 AS B),
 L5     AS(SELECT 1 as c FROM L4 AS A CROSS JOIN L4 AS B),
 NUMS AS(SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS n FROM L5)
 SELECT TOP 100000 n FROM NUMS ORDER BY n

What does this code do?  Well paste it into SQL Management Studio query window and execute it.  What do you get, a sequence of numbers from 1 to, the value in the TOP.  And it is fast.  How fast, well those 100,000 rows in the above query are returned in about 1 second.  Obviously the more rows the longer it will take.  But this is a great way to implement new Ids for a new table when initially populating data in a non-identity field.

You can also then do an inner join with this.


WITH
 L0     AS(SELECT 1 as c UNION ALL SELECT 1),
 L1     AS(SELECT 1 as c FROM L0 AS A CROSS JOIN L0 AS B),
 L2     AS(SELECT 1 as c FROM L1 AS A CROSS JOIN L1 AS B),
 L3     AS(SELECT 1 as c FROM L2 AS A CROSS JOIN L2 AS B),
 L4     AS(SELECT 1 as c FROM L3 AS A CROSS JOIN L3 AS B),
 L5     AS(SELECT 1 as c FROM L4 AS A CROSS JOIN L4 AS B),
 NUMS AS(SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS n FROM L5)
 SELECT TOP 1000 n
 FROM NUMS
 WHERE n NOT IN (
 SELECT TOP 10000 n
 FROM NUMS nu
 INNER JOIN tableName t on nu.n = t.TableId
 ORDER BY n)
 ORDER BY n

By joining in the above table, what this does is give me a list of ids that aren’t in the tableName table.  So I am able to populate new ids where old one have been deleted and I am not wanting to just keep adding the numbers on top.

I have used this a couple of times to provide a very easy way in ad-hoc queries to get numbered data like it returns.  Try it and let me know the use you have for it.

Advertisements

Posted on March 20, 2012, in Tips and Tricks and tagged . Bookmark the permalink. Leave a comment.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: