You can tell that's a very old entry - the description of how a heap scan works is wrong!

Jeff Moden (11/15/2010)

---

I have no idea how I got it to work before but I did get it to work and I can't find the code to prove it and...

Scott,

The good news is that you are seeing minimal logging, but read on.

Minimal logging means that SQL Server does not log the individual row changes; it logs changes to pages...

RichardDouglas (11/15/2010)

---

Paul White NZ (11/14/2010)

---

Delete the row and then re-insert it with the correct identity value specified explicitly.

You will need SET...

How do you know the target table does not have enabled triggers, check...

Well I suppose there are...

I'll look forward to it.

Code to demonstrate the issue, and show that UDFs that access data cannot be persisted:

CREATE TABLE dbo.Data

(

...

You could use a UDF that references the other table, but please don't. Functions...

Dave Ballantyne (11/15/2010)

---

I believe the difference here is in the hash match operation , in your version it is matching on 2 columns and in Jeff's 1 column. Hash...

tommyh (11/15/2010)

---

I did figure it would be partly because of the much larger dataset. I had just assumed that something in the execution plan regarding cost (CPU/IO/Operator) would differ more...

DECLARE @ToDrop SYSNAME,

@sql NVARCHAR(MAX);

SET @ToDrop =

...

There are a number of differences, but the three most important are:

1. In the Compute Scalar immediately above the Clustered Index Scan, the plan with the extra GROUP BY...

Definitely: Create the table with IDENTITY property and (only) the Clustered Index before loading. Ensure the destination database is in BULK_LOGGED or SIMPLE recovery (to enable minimally-logged bulk load).

Configure...

upstart (10/27/2010)

---

Would you ever want to have a Non-Clustered and Clustered Index that are the same? For some reason, I inherited several tables that have NC and C indexes...