Right. It took a fair amount of work to get to see the plans there, but I now see what you mean.

The APPLY version is much less efficient than...

About the 'internals'...

The first thing to realize is that the trick of generating a comma separated string using multiple assignments takes advantage of an undocumented quirk in SQL Server. ...

Use a CTE or derived table in place of the Employee table. A DISTINCT or...

On the toll-call proposal: I think it is important we remember whose idea it was and direct any royalty payments appropriately :laugh:

Why don't you try both and let us know what differences you find?

It is certainly possible...

I assume the percentage figures you quote are the cost % of the batch? I don't generally attach much value to those percentages, but it'd be great to see...

Tom.Thomson (1/10/2010)

---

They can't in theory occur within a transaction, they...

Mitch,

The code I posted was to demonstrate technique - it was not intended as a complete solution.

To be fair, I think you could easily have found the solution for yourself...

It's not always about the performance of one query.

Quite frequently, concurrency is more important, in which case a cursor is a fine choice.

A quick-and-dirty solution is to just add an extra period to each string during processing. Your script then runs without modification:

DECLARE @data TABLE (data VARCHAR(50) NOT NULL);

INSERT @data (data)...

Here's one way of obtaining the sort of results you seem to be after. It is based on data in the AdventureWorks sample database.

SELECT TOP (10)

...

The internal worktable created to hold a copy of the rows is not a normal temporary table and can't be accessed by users.

Normally, yes - but only in the circumstances I described....

Indeed. i was thinking of an 0900 number that charges...

CirquedeSQLeil (1/9/2010)

---

That is extremely annoying. Some PMs and some emails is one thing - but then to go to the next medium. If it is somebody I know,...