Looks like this answer on StackOverflow is relevant to you.

Sql Server: Selective XML Index not being efficiently used

Have a look at using a secondary selective xml index.

Carlo Romagnano (3/17/2014)

---

Mikael Eriksson SE (3/17/2014)

---

I guess the reason for the rollback is that the output clause is not allowed when you do remote queries.

Using a query like this will...

I guess the reason for the rollback is that the output clause is not allowed when you do remote queries.

Using a query like this will show the the generated error...

There is one more thing to notice about the first query.

You assign the value "2000-02-01 12:34:56.789" to @EndDT but the query returns "2000-01-02 12:34:56.789".

Either make sure you are using "SET...

There is only one correct answer to this question.

You always need an order by on the select statement if you want the rows ordered in the resultset.

Sometime they may be...

The point I was getting at was not...

When you ask for a recompile of the stored procedure it is recompiled on the next execution. The compiled value of @Name for the query will then be what is...

You need to make sure that the query plan is not already generated and in cache before first execution of the procedure.

Using sp_recompile 'GetIt' will make the entire stored procedure to be recompiled on next execution.

sp_recompile 'T' recompiles only the queries in the stored procedure that references the table T (statement...

Check this post for a query the fetches the values from the execution plan.

url=http://www.sqlservercentral.com/Forums/FindPost1505494.aspx

It is ParameterCompiledValue.

No.

That is as it should be. The question was about what value was used as input to the query optimizer when...

It is the same in SQL Server 2005, 2008 and 2012.

If you want to check what the compiled value is you can use this.

create table T(Name varchar(25));

go

create procedure GetIt

...

You should use sp_executesql with parameters.

Have a look at The Curse and Blessings of Dynamic SQL for a bit more info.