Wayne, it really depends. There seems to be a tipping point where a dynamic tally table beats a physical tally table.

Don't have specifics, and it could just be my...

Brian, would you please post your final solution? I would like to see what you tweaked.

I usually make things more complicated than they need to be to start, so...

Stock or Products? 😉

Unless you are having disk space issues requiring you to make your database(s) small, don't shrink them. They will only have to grow later as additional data is added...

That's okay, I think I fixed it!

Here is the...

How about this as a solution?

SET NOCOUNT ON

CREATE TABLE #Sample

...

Grant Fritchey (1/24/2011)

---

Jeff Moden (1/24/2011)

---

Absolutely awesome....

Does the table have a clustered index on it when you build the NC index?

Sorry, lost. You tests show that if you don't specify the size, it won't go below the default size. If you indicate a size that is smaller that...

Something like this:

CREATE TABLE [dbo].[tblCost](

[Unit] [varchar](6) NOT NULL,

[ApproverID] [char](6) NULL,

) ON [PRIMARY]

delete from [tblCost];

CREATE TABLE [dbo].[tblApprover](

[Unit] [varchar](6) NOT NULL,

[ApproverID] [char](6) NULL,

) ON [PRIMARY];

INSERT INTO dbo.[tblCost](Unit, ApproverID)

VALUES ('12345', 'Pa0004'),

('23456', 'Pa0002');

INSERT INTO...

I have to ask the same question, why the dynamic sql. From what I can see it isn't needed.

Haven't tested it, but shouldn't the code look like this?

SELECT AccountNbr, TransactionDate, TransactionAmount FROM

(

SELECT

AccountNbr,

TransactionDate,

...

Just in case someone wants to play with my code, here it is.

create function dbo.ChangeDelimiters (

@pLine varchar(max),

@pSDim char(1),

@pRDim...

Anyone have some code to generate a million row table for this particular problem? My brain isn't quite functional at the moment, but I may have a solution that...

Brian McGee-355525 (1/24/2011)

---

BTW in terms of extra information or DDL I'm not sure what people want.

CSV data with a fixed number of fields (in my case, 13), and an unlimited...