Steve,

Thanks - you wouldn't happen to know the exact key would you? I've looked through all...

Steve,

So, from a technical perspective (though obviously not a legal one) it doesn't make any difference what licence mode you select when you install SQL Server, because you can go...

Robin,

This may work in Management Studio (SQL 2005) but this features is not available in SQL Query Analyzer (from SQL 2000).

The clue's in the title of this thread! 😉

Adam

And when I revert to the original SQL, it goes off and finds the old cached version, and uses that again, and doesn't generate a new plan? I doubt it.

I've...

The performance of the query, when the varaibale passed to the SP is copied into a new local variable is fine - it takes less than a second.

I'm more concerned...

Sergiy,

Thanks - I'll give it a go.

Regards

Adam

Sergiy,

No, there are 68 records in tblPrompts, which have a LabelID of 27 in tblPromptLabels (which is a join table for a many-many join) that also don't appear in the...

Sergiy,

Yes, the Hash Tables version runs the fastest

tblPrompts has 15167 rows

tblPromptTranslations has 244783 rows

tblPromptLabels has 162010 rows

The result of the query has 68 rows.

Regards

Adam

Sergiy,

Here is the SQL for the two versions of the SP, the code used to call it, and the generated execution plans.

It is displaying exactly the same behaviour as shown...

Phil,

Thanks - that is useful.

I've found something very strange doing this. See the SQL and the execution plans below. It makes a big difference to the execution plan if I...

Steve,

I wondered if it was just creating a new plan, but I tried removing the new variable, and it reverted back to the old Execution Plan, so it really is...

Paul,

The way it stopped working (even though it was previously fine) is another symptom I noticed.

After I fixed this SP, I found another with the same problem, although several others...

Yes, and it doesn't make a difference.

Adam

But I tried all that, as I said in my original post

I ran 'ALTER PROCEDURE', I ran sp_recompile, I even dropped and recreated the SP, and this didn't make a...

Paul,

That works! Thank you 🙂

It makes no sense though - why should it make such a difference?

Thanks

Adam