Hey! That's the same code I posted but without the TOP 3 or the final ORDER BY! :laugh:

Yes start-up filters are a favourite of mine too. They come in handy for all sorts of things (conditiional joins, for example). I've found myself using them a...

Gosh 🙂

Yep, good advice! 🙂 😀 😛 😉 :w00t: 😎 :hehe: :angry: :crazy: :doze: :laugh: :rolleyes: :satisfied: :blink: :ermm: :crying: :pinch: :kiss: :Wow:...

For general advice, and a solid methodology that works, read This Microsoft White Paper.

The documentation for DBCC TRACESTATUS is not particularly well-phrased.

The fact is that a simple:

DBCC TRACESTATUS;

...will show all trace flags enabled for the current session, regardless of whether the trace flag...

This is the part that seems remarkable to me.

To...

Absolutely. The task of the query optimizer is quite hard enough, without it being fed...

Just to demonstrate the advantage of OPTION (RECOMPILE):

Note: SQL Server 2008 SP1 CU5 (build 10.00.2746) or later required

USE tempdb;

GO

-- Test table

CREATE TABLE [dbo].[A]

...

Barry,

I'm so glad it makes sense to you now!

This has been an excellent thread I think, mainly for the quality of the discussion that led on from the original question....

William Plourde (3/3/2010)

---

After thinking about this for a minute I was a little confused about why everyone is trying to use a ranking function when the data is already 'rankable'...

Unless the procedure is called very frequently, try adding WITH RECOMPILE to its definition. That will re-optimize the procedure for the current parameter values at the cost of, er,...

Your output data example is wrong :doze:

Either of the following alternatives will work:

SELECT InputSet.ID1,

iTVF.ID2,

...

So it does. Never noticed that before. Good news!

I say things I should only think sometimes :blush: :w00t: