Jennifer,

Contrary to what many have posted above, there is absolutely nothing wrong, not even the thing about the aliases,  with your code.  It is very likely a simple matter of...

As you are aware, performing just about any calculation on an indexed column will negate any chance of using the index.  Try this... it's simple and makes for nasty fast...

First, using a cursor  or a WHILE loop  just isn't an option for me... I just gotta do...

John and Jeffrey,

Thank you both for taking the time to read the post and understand it   Don't be too disappointed if you can't...

Then, I'm thinking that someone didn't design the database correctly.  It's hard for me to believe that anyone would make a permanent table that has over 520 some odd...

"SELECT INTO" is actually a setting called "SELECT INTO/BULK COPY" and it does NOT disable the ability to use the SELECT/INTO syntax.  It just means that it will be logged. ...

Jeffrey,

First, I say "you" a lot in the following write-up... I don't mean you personally and hope you don't take it that way (personally).  Your questions are outstanding and instead...

I believe the reason why the Numbers or Tally table solution is so much faster is two-fold.

1. As Jesper pointed out, the Numbers table will be indexed on the "N"...

If your going to post things like this, you might also take the time to post the reasons

Jeffrey,

As you are probably aware (you ARE an experienced CIO), any RBAR (a "Modenism" for "Row By Agonizing Row", pronounced "ree-bar") is ALWAYS slower than an equivalent set-based operation.  Cursors...

What is the datatype of the column you call "Activity Date"?  I have the sneaky suspicion that it's not a DateTime column and need to know what datatype it is...

SELECT IDENTITY(INT,1,1) AS RowNum,*

    INTO #yourtemptable

    FROM #yourpermtable

Don't forget to add a primary key for speed and accuracy...

The author of the article wrote: "but using temporary table you are increasing disk activity since TV (Table Variable) is created in memory as opposed to Temp tables (created in...

Ummmm.... wanna tell me what version of SQL Server there was that didn't have cursors?

Mike C,

Exactly right and great call.  In fact, there's documented proof that table variables do use TempDB at the following web site and that there are some serious DISADVANTAGES to...