November 24, 2008 at 10:39 am
Thanks for this! I'm looking forward to the next installment: "using it to generate a time coordinate table."
November 24, 2008 at 11:02 am
Adam Machanic (11/24/2008)
Joe Celko (11/24/2008)
Here is another method, which uses a tale of digits and some mathCREATE TABLE Sequence (seq INTEGER NOT NULL PRIMARY KEY);
WITH Digits (i)
AS
(VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9))
INSERT INTO Sequence (seq)
SELECT 1 + D0.i
+ (10 * D1.i)
+ (100 * D2.i)
+ (1000 * D3.i)
+ (10000 * D4.i)
+ (100000 * D5.i)
+ (1000000 * D6.i)
FROM Digits AS D0, Digits AS D1, Digits AS D2, Digits AS D3,
Digits AS D4, Digits AS D5, Digits AS D6;
Cute -- but I can't get VALUES to work properly in a CTE in SQL Server 2008. Have you tested this there? I'm not sure why it doesn't work as-is; it seems to work just fine in a derived table, or in the following reconstruction of your code:
WITH Digits (i)
AS
(SELECT i FROM (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) x(i))
INSERT INTO Sequence (seq)
SELECT 1 + D0.i
+ (10 * D1.i)
+ (100 * D2.i)
+ (1000 * D3.i)
+ (10000 * D4.i)
+ (100000 * D5.i)
+ (1000000 * D6.i)
FROM Digits AS D0, Digits AS D1, Digits AS D2, Digits AS D3,
Digits AS D4, Digits AS D5, Digits AS D6;
Use UNION ALL instead of VALUES:
CREATE TABLE Sequence (seq INTEGER NOT NULL PRIMARY KEY);
WITH Digits (i)
AS
(SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9)
INSERT INTO Sequence (seq)
SELECT 1 + D0.i
+ (10 * D1.i)
+ (100 * D2.i)
+ (1000 * D3.i)
+ (10000 * D4.i)
+ (100000 * D5.i)
+ (1000000 * D6.i)
FROM Digits AS D0, Digits AS D1, Digits AS D2, Digits AS D3,
Digits AS D4, Digits AS D5, Digits AS D6;
November 24, 2008 at 11:04 am
You could also create tables with different number bases e.g for binary:
WITH Digits (i,s)
AS
(
SELECT 0 AS [Value], '0' AS String UNION ALL SELECT 1, '1'
)
SELECT (D1.i)
+ (2 * D2.i)
+ (4 * D3.i)
+ (8 * D4.i)
+ (16 * D5.i)
+ (32 * D6.i)
+ (64 * D7.i)
+ (128 * D8.i) [Int],
D8.s + D7.s + D6.s + D5.s + D4.s + D3.s + D2.s + D1.s [String]
FROM Digits AS D1
CROSS JOIN Digits AS D2
CROSS JOIN Digits AS D3
CROSS JOIN Digits AS D4
CROSS JOIN Digits AS D5
CROSS JOIN Digits AS D6
CROSS JOIN Digits AS D7
CROSS JOIN Digits AS D8
ORDER BY 1
November 24, 2008 at 11:31 am
Ok, so you populated a table with numbers. Everyone can argue about how to do that, but the real question is what do you DO with the table of numbers? Why isn't this just a longer post?
November 24, 2008 at 11:37 am
there seem to be some very ungrateful (and even rude) posts here! and it's completly uncalled for!
November 24, 2008 at 11:37 am
Ed W. (11/24/2008)
Ok, so you populated a table with numbers. Everyone can argue about how to do that, but the real question is what do you DO with the table of numbers? Why isn't this just a longer post?
Fantastic question! Here are a few ideas (did I mention, I'm obsessed with numbers tables?):
http://sqlblog.com/blogs/adam_machanic/archive/2006/07/12/dealing-with-very-large-bitmasks.aspx
http://sqlblog.com/blogs/adam_machanic/archive/2006/07/12/pattern-based-split-string.aspx
http://sqlblog.com/blogs/adam_machanic/archive/2006/07/12/reflect-a-tsql-routine.aspx
http://sqlblog.com/blogs/adam_machanic/archive/2006/07/12/tokenize-udf.aspx
... and that's mainly just string manipulation. You can also use them to solve gaps and islands problems, build calendar tables, generate sequenced test data... The list goes on and on.
--
Adam Machanic
whoisactive
November 24, 2008 at 11:46 am
Ed W. (11/24/2008)
Ok, so you populated a table with numbers. Everyone can argue about how to do that, but the real question is what do you DO with the table of numbers? Why isn't this just a longer post?
I didn't want to constantly talk about creating a numbers table every time I walk about how to use one.
November 24, 2008 at 11:47 am
sho (11/24/2008)
there seem to be some very ungrateful (and even rude) posts here! and it's completly uncalled for!
I agree. But then again, maybe I'm biased.
November 24, 2008 at 12:02 pm
Some people just have to complain. Good article. Jeff Moden also wrote an article on the Tally table here on SSC. If you haven't read it, you might want to look it up. I'd go through the Authors link and find Jeff Moden. If I remember right, he is in the list of authors with 9 articles.
November 24, 2008 at 1:37 pm
declare @int table (n char(1))
insert @int
select '0'
union select '1'
union select '2'
union select '3'
union select '4'
union select '5'
union select '6'
union select '7'
union select '8'
union select '9'
insert util_nums
select convert(int,n1.n + n2.n + n3.n+ n4.n + n5.n + n6.n) from @int n1,@int n2,@int n3,@int n4,@int n5,@int n6
this took about 9 seconds to run on :
sql server 2000 dev edition
windows xp professional
pentium 4 -- 3GHz 2GB RAM
it's actually my desktop
the code above comes from Spetic, Gennick "transact-SQL cookbook"
GC
November 24, 2008 at 1:58 pm
Thanks Adam Machanic and CheeseMan316. I hadn't seen numbers tables before and now I have a lot of good resources to look at!
November 24, 2008 at 2:41 pm
I must be missing something here. The thing I don't get about the whole tally of numbers discussion is why so much time and effort seems to always be devoted to demonstrating fancy ways to create a hellishly simple table. Given that these things have a million uses, why not just create one as a matter of course when setting up your server? That way it's always there when you need it. Ten seconds vs ten minutes as a one-off exercise over the life of the server is an academic argument.
As clever as some of the methods for creating a million rows are, the real value in this discussion will come from showing how to use them to replace RBAR loops.
Regards
GPO
November 24, 2008 at 5:09 pm
The article was well written, but it is worth noting that Jeff Moden described a more set based approach to creating the same thing in http://www.sqlservercentral.com/articles/TSQL/62867/
---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/
November 24, 2008 at 5:13 pm
jacroberts (11/24/2008)
Performance isn't always an issue as in my previous example.
I agree with Jeff that performance should always be a concern. After all, you may eventually need that report urgently updated for the CEO from more recent data, or the server may simply find itself with less and less "maintenance time" without heavy user concerns.
With that said, there are times when the amount of developer time it would take to improve performance is more important than the performance is. For instance, it does not make sense to spend hours optimizing an ad hoc report that will likely never be run again (though it does make sense to save it in case never actually arrives.)
---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/
November 24, 2008 at 5:24 pm
timothyawiseman (11/24/2008)
jacroberts (11/24/2008)
Performance isn't always an issue as in my previous example.I agree with Jeff that performance should always be a concern. After all, you may eventually need that report urgently updated for the CEO from more recent data, or the server may simply find itself with less and less "maintenance time" without heavy user concerns.
With that said, there are times when the amount of developer time it would take to improve performance is more important than the performance is. For instance, it does not make sense to spend hours optimizing an ad hoc report that will likely never be run again (though it does make sense to save it in case never actually arrives.)
I disagree, performance isn't always an issue though it sometimes is. Anyway, the method I listed did show reasonable performance, in fact the performance for small number ranges is much better than the other examples listed, as there is no table/index scanning to do you just request the number range you require and only that number range is generated. For example, if you are reporting on a month's data and you need a number for each day you would only request a table with the range 1 to 31. This would return a table with just 31 rows and would perform faster than a CTE that generates 1 million rows by default or a permanent table with 1 million rows in. Performance is often an issue and badly written and when used code can grind a system to a near halt but as in my first example performance isn't always an issue and anyway this method gives better performance for small number ranges.
I've worked on and improved the performance of a lot of different SQL systems and I know when performance is important and when it is not. Generally when you have some SQL that is called a lot of times or some SQL that takes a long time to execute it is important to consider performance. If you have a daily report that is run in batch, with no user waiting at the end of a button for the result, it doesn't matter if the SQL takes 3.2 seconds instead of 3.1 seconds to run. You just need common sense to work that out. Highly optimised code can be difficult to understand and so be difficult to maintain which can cause longer development times, maintainability issues and cause bugs to be introduced onto a system. Also, a method that is optimised for one type of query will not necessarily be optimised for a different type of query. For example, if you have a numbers table with 10 million rows in which is really efficient for some large query you might find if you use the same method on a query that only requires 30 rows it might be really inefficient.
So you can think what you like but performance isn't always a concern. My one example where it isn't a concern has proved your 'always' comment is wrong.
Viewing 15 posts - 31 through 45 (of 106 total)
You must be logged in to reply to this topic. Login to reply