Glad I could help.

Let's see if this comments help you understanding what goes on behind the scenes:

-- Variable: sql string to execute

DECLARE @sql nvarchar(4000)

-- Variable: sql string to test for...

No, the two queries get absolutely different plans when unioned together.

That's what I don't understand. How can UNION ALL change the plan so dramatically?

As a side note, yesterday (when I...

Replace your code with this:

SET @sql = '

SELECT CAST(co.InvoiceNo as varchar(10)),

CAST(co.CustomerNo AS VARCHAR(10)) + '' - '' + co.CustomerName,

...

What error are you getting?

Paul White NZ (7/1/2010)

---

In fact, scalar functions and multi-statement table-valued functions are cached almost identically to stored procedures (as the dynamic management views like sys.dm_exec_cached_plans will show you). Scalar...

You could test for existing rows prior to invoking sp_send_dbmail.

Something similar to this:

DECLARE @sql nvarchar(4000)

DECLARE @sqltest nvarchar(4000)

DECLARE @testResults TABLE (rowsAffected int)

SET @sql = 'SELECT someColumn FROM someTable'

SET @sqltest = 'SELECT...

I agree with Dave: get rid of that function if possibile.

If not, try adding WITH SCHEMABINDING to the function definition: it should mark the function as deterministic and avoid a...

It can't be coincidence!...

You will have to drop the primary key and re-create it nonclustered.

The primary key and the associated index have to live together and cannot be dropped/created separately.

Have you tried stopping SQL Agent first? It should prevent that prompt from showing.

Probably there's a better solution, but this is what I could put together quick'n'dirty:

-- Test table

DECLARE @Names TABLE (

FirstName varchar(50),

Surname varchar(50),

Source int

)

-- 1st set of duplicates

INSERT INTO @Names...

Standards... that's why I love formatting tools: everyone with the same formatting standards, like it or not.

BEGIN AD

We use SQLRefactor and we're very happy with it. I've seen around other...

Jeff Moden (6/30/2010)

---

Actually, it's not. It's only easier to comment out the last line with...

Thank...

Function can mean Scalar Function (slowest), Multistatement Table-valued Function (slow) or Inline Table-Valued Function (quite fast).

Functions don't have predetermined cached query plans, stored procedures do.

I think the real tie-breaker...