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
Legacy Comments
|
blindman
2010-10-29 |
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? |
|
vamsi
2010-11-24 |
re: SET BASED Number Table Create Hack why the code takes so many min to execute |
|
brettk
2010-12-17 |
re: SET BASED Number Table Create Hack Well it's 1 minute for 10 millions rows |