In my understanding

1:BCP can not do differential update, every time I need to copy the whole database?

2:SSIS for this task needs a lot of data flow for hundreds of table,...

Thank you for your great answer!:-P

After discard output,

the Client Statistics regression to 10 ms.

I think that's the problem.:-)

Thank you Paul for your interpretion.

If an resource is held X by process A, how can it be held U by process B again? That's my confusion.

This trigger is used...

Thank you, everyone.

A key lookup only output the key column is fairly odd. Without asking the developers of Microsoft, I've no idea on this:-) But if we noticed the something...

GilaMonster (5/5/2009)

---

Jeff Moden (5/4/2009)

---

I'm not sure how creating a filtered index on the Department column can be considered as a covering index on this query... it's still just an index...

Jeff Moden (5/4/2009)

---

I'm not sure how creating a filtered index on the Department column can be considered as a covering index on this query... it's still just an index on...

GilaMonster (5/4/2009)

---

No, it is covering and the optimiser should, theoretically, be picking it. Might be a bug, might be something subtle that we've missed.

If we flip the query and the...

GilaMonster (5/4/2009)

---

Can you post the execution plan please, not an image of it? I need to see the properties of all the operators.

Right click the plan, select save as. Save...

Jeff Moden (5/3/2009)

---

In that case, I'd recommend that you remove the index hint and add the proper index as a covering index instead of what you currently have as IX_0....

Here's the query plan.

That's quite obvious SQL Server doesn't leverage the knowledges of the index.

This query was abstracted from real word scenario, in which the table is loaded, but the...

GilaMonster (4/7/2009)

---

I have no objection to doing sorting in the DB, that is one of it's strengths. I have no objection with generating row numbers in the database, that's one...

...... I can't follow you.:-) I just want to discuss if I've made a right decision about generating this column in DB....

GilaMonster (4/4/2009)

---

Generally this is more appropriately done on the front end, as it's usually a formatting/display issue.

While Florian's solution works very nicely, in relational terms, the resulting rowset has...

Thank you very much. It's gorgeous!