Thx Mike, I've started my reading early. One of my questions was: how much space would the numbers table occupy?
Found that that 2G rows (2,000,000,000) is roughly 8GB in size. http://code.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=NumbersTable I'll need at the most 365 x 3000 years if the clients wanted one database that went from Egyptian pharaohs to American Presidents (yeah right) that would be roughly 1,000,000 rows. This expanded table would be roughly 4MBs (1/2000 of 8GBs) so I don't particularly have to worry about size. Since a numbers table for the US is "only" about 80,000 rows the numbers table would be less than 400K. Not bad at all. - Gil -----Original Message----- From: Mike Chabot [mailto:[email protected]] Sent: Sunday, September 26, 2010 8:08 PM To: cf-talk Subject: Re: cfoutput or cfloop? which is the more practical solution I was thinking years, but you could do days as well. It depends on what you want to group on in the output. To save you some searching, below is SQL to fill a numbers table for SQL Server, if you go that route. As I said earlier, the other methods work fine also. I tend to prefer doing as much as possible on the database server, since databases are optimized for handling data. CREATE TABLE [dbo].[numbers]( [number] [int] NOT NULL, CONSTRAINT [PK_numbers] PRIMARY KEY CLUSTERED ( [number] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100) ON [PRIMARY] ) ON [PRIMARY] declare @counter int set @counter = 0 begin tran while @counter < 8000 begin set @counter = @counter + 1 INSERT INTO numbers (number) VALUES (@counter) print 'The counter is ' + cast(@counter as char) end commit -Mike Chabot http://www.linkedin.com/in/chabot/ On Sun, Sep 26, 2010 at 2:50 PM, GLM <[email protected]> wrote: > > Maybe I don't have a clear concept of a numbers table (I'll be reading up on > them tomorrow.) but wouldn't I need a "number" to correspond to the units in > question (in this case days.) ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:337569 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
