If the source table is large and there are many duplicates, deleting all the duplicates in one go is likely to be slow and use a great deal of transaction...

In general, the way the terminology is commonly used, I would say there is no...

Hey 'Rock',

I'm not sure if you are the sort of person that reads links, but if you are, you'll find the following contain just about everything there is to know...

Jeff Moden (5/8/2010)

---

Damn... great idea. This is a "bin stacking" problem and I know people that would kill to have this as a truly scalable solution. The...

Jeff Moden (5/15/2010)

---

As a sidebar, the way I used to do this was to either BCP out the 3 pieces and put them back together with the DOS COPY command...

😀

Think:...

For completeness, here's one way to return the child node counts and list of names from those nodes using hierarchyid (same set-up code I posted earlier):

SELECT SQ1.node_id,

...

True - I missed that in the original question. Oh well.

Not to take anything at all away from Gianluca's fine implementation, here's an alternative using CASE:

SELECT T.SalesPart,

T.WCDate,

...

As and when you upgrade to a more current version of SQL Server, this is a very fast way to compute row counts without dynamic SQL:

SELECT CA.table_name,

...

I've had to do this a number of times from within SQL Server. The neatest way I have found is to create a T-SQL object (procedure or function) to...

The Dixie Flatline (5/14/2010)

---

Sihaab, thanks for posing an interesting topic. This was thought-provoking for me. One thing I'm curious about is how the optimizer...

Setup:

CREATE TABLE dbo.MissingTable

(

K INTEGER NOT NULL,

...

Very nice, Wayne 😎

I just know you'd like to see an APPLY in there somewhere, so:

SELECT B.ID,

iTVF.*

FROM @TblB...

Hugo Kornelis (5/12/2010)

---

Well, actually hints are law. The term "optimizer hint" is highly misleading; "optimizer directive" would have been better. If you use an index hint, the optimizer WILL use...