Excellent Jonathan. Thanks.

The execution plan stores all the information of what is accessed. This information is stored as XML. So you can run an XML query against the execution plan to return...

email-935809 (8/10/2010)

---

It's seems to be a network service that is running it from the server itself. I've installed ideara diagnotic manager recently but other than that no SP's, any...

Probably would have worked with ISNULL too, but COALESCE came into my head first.

You've got a query that is selecting everything from the client table to insert into the client table... I'd figure out who was doing that right away if it were...

On fairly simple queries the order of tables in the WHERE clause usually doesn't affect performance or the plan generated in any way. As queries get more complicated the order...

Looks OK to me. Are you getting an error? If so, what is it?

Not directly and not easily.

What you can do is an XQuery against the queries in cache to see which have accessed a given index, but the read values of the...

I think this does what you need.

--test table

CREATE TABLE OrderTest

(id INT,

parentid INT NULL)

--test data

INSERT INTO OrderTest

VALUES

(1, NULL),

(2, NULL),

(3, NULL),

(4, 1),

(5, 1),

(6, 2),

(7, 2),

(8, 3),

(9, 3)

--query

SELECT *

FROM OrderTest

ORDER BY COALESCE(ParentId,Id),...

Still digging through the Thread from three days off,...

Swirl80 (8/6/2010)

---

@Grant:

Not sure what you're refering to? Whats not on the same SP? We're on Service Pack 3 Cumulative Update 4 on live and Service Pack 3 Cumulative Update 5...

I notice they're not at the same SP... since this functionality was introduced with an SP on 2005, maybe you're on the wrong one?

Swirl80 (8/6/2010)

---

@Gila:

If you create this:

CREATE PROCEDURE [dbo].[spProcTest]

AS

BEGIN

CREATE TABLE #qwerty

(

id INT

)

SELECT * FROM #qwerty

END

and get and estimated plan on it then it works. I can 100% guarantee that there is...

Not sure why Get-Content didn't work for you. I tested it several times. Still, glad you solved the problem.

You can't get estimated plans on queries with temp tables because the algebrizer fails on the initial run. The recompile that occurs when temp tables are present is when the...