x002548's Blog

Not Just a Number - Brett Kaiser
posts - 89, comments - 586, trackbacks - 39

SET BASED Number Table Create Hack

Well...don't think I've seen this hack before..I guess you might call it a setbased way to create a numbers table. Who has done it this way before? USE [dba] GO CREATE TABLE n(n int) GO INSERT INTO n(n) SELECT CONVERT(int,m.n + hth.n + tth.n + th.n + h.n + t.n + u.n) AS [myDay99] FROM (SELECT '0' AS [n] UNION SELECT '1' AS [n] UNION SELECT '2' AS [n] UNION SELECT '3' AS [n] UNION SELECT '4' AS [n] UNION SELECT '5' AS [n] UNION SELECT '6' AS [n] UNION SELECT '7' AS [n] UNION SELECT '8' AS [n] UNION SELECT '9' AS [n] ) AS u CROSS JOIN (SELECT '0' AS [n] UNION SELECT '1' AS [n] UNION SELECT '2' AS [n] UNION SELECT '3' AS [n] UNION SELECT '4' AS [n] UNION SELECT '5' AS [n] UNION SELECT '6' AS [n] UNION SELECT '7' AS [n] UNION SELECT '8' AS [n] UNION SELECT '9' AS [n] ) AS t CROSS JOIN (SELECT '0' AS [n] UNION SELECT '1' AS [n] UNION SELECT '2' AS [n] UNION SELECT '3' AS [n] UNION SELECT '4' AS [n] UNION SELECT '5' AS [n] UNION SELECT '6' AS [n] UNION SELECT '7' AS [n] UNION SELECT '8' AS [n] UNION SELECT '9' AS [n] ) AS h CROSS JOIN (SELECT '0' AS [n] UNION SELECT '1' AS [n] UNION SELECT '2' AS [n] UNION SELECT '3' AS [n] UNION SELECT '4' AS [n] UNION SELECT '5' AS [n] UNION SELECT '6' AS [n] UNION SELECT '7' AS [n] UNION SELECT '8' AS [n] UNION SELECT '9' AS [n] ) AS th CROSS JOIN (SELECT '0' AS [n] UNION SELECT '1' AS [n] UNION SELECT '2' AS [n] UNION SELECT '3' AS [n] UNION SELECT '4' AS [n] UNION SELECT '5' AS [n] UNION SELECT '6' AS [n] UNION SELECT '7' AS [n] UNION SELECT '8' AS [n] UNION SELECT '9' AS [n] ) AS tth CROSS JOIN (SELECT '0' AS [n] UNION SELECT '1' AS [n] UNION SELECT '2' AS [n] UNION SELECT '3' AS [n] UNION SELECT '4' AS [n] UNION SELECT '5' AS [n] UNION SELECT '6' AS [n] UNION SELECT '7' AS [n] UNION SELECT '8' AS [n] UNION SELECT '9' AS [n] ) AS hth CROSS JOIN (SELECT '0' AS [n] UNION SELECT '1' AS [n] UNION SELECT '2' AS [n] UNION SELECT '3' AS [n] UNION SELECT '4' AS [n] UNION SELECT '5' AS [n] UNION SELECT '6' AS [n] UNION SELECT '7' AS [n] UNION SELECT '8' AS [n] UNION SELECT '9' AS [n] ) AS m ORDER BY 1 GO

Print | posted on Friday, October 29, 2010 12:29 PM | Filed Under [ SQL Gimmicks ]

Feedback

Gravatar

# re: SET BASED Number Table Create Hack

Other than a few minor tweaks, I don't see anything horrible about this at all. There must be a dozen different ways to create a numbers table, that will be populated only once, so who cares whether the code is the absolute most efficient it can possibly be?
10/29/2010 1:49 PM | blindman
Gravatar

# re: SET BASED Number Table Create Hack

why the code takes so many min to execute
11/24/2010 5:33 AM | vamsi
Gravatar

# re: SET BASED Number Table Create Hack

Well it's 1 minute for 10 millions rows
12/17/2010 10:30 AM | brettk
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET