A CTE declaration can only be used once, so if you need to SELECT out of the CTE#1 *and* then re-use it in a subsequent CTE, maybe you'd be better...

Are any of the non-clustered indexes perhaps better candidates for being clustered ?

Do any of them have some naturally ascending type of data in them, eg dates.

Example here:

http://msdn2.microsoft.com/en-us/library/ms186243.aspx

CTE has an "anchor" statement, UNION'ed with another SELECT that references the CTE.

Ahh, it wasn't clear that it was 400K records into an existing very large table. I thought it was creation of a new table with an initial 400K records.

OK, so...

>>Which table do you mean when you "inserted table"? The table I'm inserting into, or inserting from?

The table being inserted to.

Can you remove the indexes and primary key constraint prior...

Things to consider:

Are there any triggers on the table being inserted ?

Is there a clustered index on the inserted table and does the clustered index include the GUID column ?

How...

Try running DBCC UDPATEUSAGE() on the database to ensure the system cataolog tables are up to date.

Also, have you applied service pack 1 or 2 yet ? There was a...

Looks to me like all 4 SELECTs are exactly the same, with just a different filter on T_HISTORY.ORIG_DT based on the input parameters.

Do you really need IF .. THEN blocks...

[Edit] Nevermind.

It wasn't an entirely fair comparison, because I hard-coded the ROW_NUMBER() solution to use TOP 100.

I'd expect the CTE to win out for smallish values of "EngCount" because it only...

Hey, all it took was 1 cup of coffee:

[font="Courier New"]With CTE_Expand

As

(

Select CustomerID, EngID, 1 As EngNumber

From @Table

Union All

Select c.CustomerID, c.EngID,...

Nice 🙂

Don't forget, though, in SQL2005 we don't need to use permanent tables and IDENTITY columns to generate our numbers for us. Use ROW_NUMBER() function and a derived table on...

Microsoft KB article on the subject:

http://support.microsoft.com/kb/906344

Nothing to be concerned about.

Looping and inserting 1 record at a time is never going to give you the performance you require.

I would revert to bulk insert to a staging table, and then remap...

Cue gnashing of teeth ...

The columns PART_ID DATE PART_CD  REN_CD are part of PK

But, but ... if PART_CD is part of the key, then in the example you...