Jeff Moden (4/20/2016)

...

What's odd is that the "like @var " comes up with a seek predicate that's different than the predicate. The Seek Predicate comes up with a >...

TomThomson (4/21/2016)

---

Hugo Kornelis (4/21/2016)

---

From the explanation:

TRY_CONVERT should obey the CAST and CONVERT rules, which note that truncated values are returned as an E for char and varchar. However, a NULL...

roger.plowman (4/21/2016)

---

When I run that select statement in SQL Server 2008 (10.0.5538) I get the error:

SELECT TRY_CONVERT(NVARCHAR(1), 234523)

Msg 195, Level 15, State 10, Line 1

'NVARCHAR' is not a recognized built-in...

I'm glad that seems to be working for you!

By "code tags" I just mean putting before your code, and [/code!] after it (with the '!' removed from both; I...

No problem. It's easy to get turned around when working on a problem like this 🙂

Here's one try. Based on the verbal description of the rules I think this is...

That's what I thought at first, but I don't think so now.

Even if the string is correctly specified to unicode, I see the same behavior.

It actually appears that WHERE...

A couple things.

1) The posted script for creating the data won't parse (a typo in "integer" and no commas separating the VALUES for the first INSERT).

2) Both the total costs...

Since the thread is almost three weeks old, you've probably already figured this out on your end, but I figured I'd mention a couple things for thoroughness.

In the initial description...

To continue the crusade for fairness, now that we're looking at time and not just IO, I'd probably want to remove the ORDER BY Id that I threw in to...

Just save it as a .xml file and read it with your favorite XML reader.

Most of them should format it pretty nicely (even just opening it with IE does...

Just to make that a little fairer (against an actual table the old-school one will of course have to do more than 0 reads :-)):

SET NOCOUNT ON;

CREATE TABLE #temp (ID...

That's because it's casting the text plan as XML in the SELECT list. Just pull the query plan column without casting it to XML.

Cheers!

Hugo Kornelis (4/20/2016)

---

The...

Careful, that won't be guaranteed to be correct if the data is ever changed to datetime2 with a higher precision than datetime.

Might as well use Jeff's, which will work regardless...

Then that sounds like the first problem I mentioned with the nesting levels, not the problem with the branches.

Either way, did you try using sys.dm_exec_text_query_plan? That should get around the...