It's the alias for the derived table... means "New Start Date"... just a name I gave it.

Outstanding question! And, sadly, I have no idea why... but it certainly explains why SELECT/INTO is faster even in the FULL recovery mode.

I did it on a 3 column...

CTE is nothing more than a "derived table"... try this...

--===== Do the update using the new "start date" and add 1 to that for new "end date"

UPDATE s

...

Heh... shouldn't have posted in the SQL Server 2005 forum then... 😉

Lemme see what I can do...

The following is from Books Online and should help you...

Logged and Minimally Logged Bulk Copy Operations

When using the full recovery model, all row-insert operations performed by bcp are logged in...

So, what was the problem?

Either you're not using SQL Server 2005 or your compatibility mode is set to "8.0".

Please check.

2k5 has Synonyms... much faster to flop a synonym between two tables.

You really shouldn't use ISQL.exe... use OSQL.exe instead. Also, as suggested, BCP is likely the way to go here.

I'm pretty sure indexing won't do much good on this because of all the formulas in the join and the Row_Number partition/order by.

Why must it be ordered by the LEN?

Setup profiler to page, call, email, and throw rocks at the hardware team everytime a query takes longer than a certain time to execute... you sleep. 😛

If you're going to do it more than once in the same proc, make a temp table and join to it because IN, OR, and INNER JOIN are pretty much...

You used a colon instead of a semi-colon... "Must look eye" 😉

... and here's how to build/use one...

http://www.sqlservercentral.com/articles/TSQL/62867/

Slawek (5/20/2008)

Well, if you convince me that fixing indexes will improve SAN performance, then I'll drop all my tests with multiple files and start fixing indexes 🙂

My problem is that...