I posted the same question at Connect. One of the MS reps gave me a good response.

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=244214

I've been burned way too many times by poor use of functions to be any kind of advocate for them. However, if you really want to drill down on them,...

The queries work well when the data is loaded, but don't work when the tables are empty? I think the previous post may be right, the execution plans are still...

Doggone it! I was getting excited that I might be able to tell you something rather than ask you something.

😛

As to your initial question, it's two schema's in one database, not two databases.

As to your further explanation that you'll be bringing other applications online. As the other applications come...

Any performance degredation is not linear. Depending on how you're working with the system, there may be very little degredation at all. In the instances within the article, we were...

Personally, I would have two schema's within the same database. This will allow you to lock down one schema, but have referential integrity between the schemas where as you would...

You know if you're getting different plans and different performance, you might have instances where one piece of data generates a plan that it can use and a seperate set...

I think I'm losing track of the thread. Where are you with this? Have you tried changing the indexes in any of the ways that have been proposed? Have any...

This is great! What a lot of fun, and really good information. I do like the DBA in traditional DBA garb doing the performance dance.

Agreed on using JOIN statements instead of IN statements.

Do you really need the DISTINCT? That's usually added when the data is bad, the structure is bad, or the where clauses...

Unless they're they're the only things in the field, then you could get away with:

SELECT...

FROM...

WHERE MyField = 'Project Managers Book'

OR MyField = 'The Project Book'

...

There was a time when I was recommending using this code to eliminate dynamic SQL and still have a multi-valued search query:

SELECT....

FROM....

WHERE...

AND CASE WHEN @LocationTypeId...

For literally the same index, COLA, COLB and COLA, COLB, no, I can't support that. I'd even say that's unsupportable if the first one was clustered and the second one...

Have you looked at the execution plans for the those specific queries? Scans, hash joins, cursors, anything like that in evidence?