Writing a function to do that is straightforward - but if it's running a TOP 5 against the table for every input row, the performance could be dire! The tricky...

Yes, your TOP 5 limits records to the top 5, not the top 5 per customer/item. You could do a correlated TOP 5 subquery but I wouldn't recommend it. There are...

Sergiy, when you give directions I bet you say 'No, I wouldn't start from here'. Let's try and be a bit constructive.

 

>1. Checking flags. Because there are only 2...

you can use SET SHOWPLAN or SET STATISTICS to generate a textual query plan. Have a look at BOL.

sql-server-performance.com might have some stuff on optimisation. For database design theory, you...

I agree that you should use the new JOIN syntax to make the code easier to read, but it won't necessarily make any difference to the query plan - it certainly...

<database>.<owner/schema>.<table>,

e.g. select * from newdatabase.dbo.table1

Yes that is what happens to temp tables created in dynamic SQL. That's why I suggested creating a stub of a temp table in the calling process, which the dynamic SQL...

So if you create a sp which references a table that doesn't exist, then create the table afterwards, the dependency tracker spots it? i.e. it doesn't just use system tables,...

You would have to use dynamic SQL to specify which DB to use. And for good reasons, you can't use dynamic SQL in a function.

How many databases do you have...

So if you change the sp to just return a row of hardcoded data, it works OK?

So to clarify, you want to get all those customers whose latest order is for a larger quantity than the average for that customer's last five paid orders? Should we...

I would guess that the difference in your query times is just because of the difference in number of rows processed. Do the other combinations return very few rows?

The left join...

Dynamic SQL would give you insurmountable problems in getting dependencies from programmable objects' code. A complete SQL parser would be handy. In the absence of that, a starting point might...

Ah yes, nested views:

>Of course I don't know how many tables of what size are in your views, and what optimisation they might bear, or their parents, or their parents' parents...

I don't think you can do much except ask for your login to be given access to MSDB. I suppose you could ask for a stored proc or view to be...