But, of course, never on a production system where we need to have up to the minute data recovery, right.

100,000 records is just going to take a long time to return especially when you add x number of columns, latency, network traffic, etc. It's also probably going to ignore...

OK. Maybe I'm wrong.

I tried several things and the original is still the performing best.

I thought this would work:

SELECT *

FROM x

WHERE 1 = CASE WHEN '' = @id THEN...

Oh yeah, I'd go with XML. It's a bit of a pain to code, but you can make it scream (except for the memory overhead) for performance. It directly supports...

First, the low hanging fruit. Is the column Enquiry_Id an integer (most ID columns are, why I'm asking)? If so, it looks like you're passing a string & letting it...

There's no doubt that XML does add overhead, but from the sounds of things, you're probably well within the range that justifies it's use. What kind of functionality is it...

Do you mean that you've passed in more than 2100 parameters? If that's true, you might want to change your calls and send in XML that you can shred on...

#1 DBA Response: It depends.

Honestly, some production systems don't need up to the second transaction recovery so we set the recovery model to simple. Some production systems, we care about...

You really need to evaluate the system. Bare minimum you need to be sure the statistics are getting updated regularly. You should also look into tracking down a script that...

You really need to evaluate the system. Bare minimum you need to be sure the statistics are getting updated regularly. You should also look into tracking down a script that...

We spent several days chasing down why a query was running twice as fast on a test box as it ran in production. Then we noticed that the test box...

Always stored procs.

If the order or where clause is radically variable, you may have to generate a dynamic statement within the stored procedure (totally frowned on, but possibly necessary).

Think about...

Depending on your experience, this could be a tough transition. You need to learn to work with and like developers. I'd work on db design, advanced tsql programming, xml, and...

The only time we've maxed out at my current company, and we have, was when we built a really bad database that pegged the cpu's on a 4-way server. Some...

HAVING MAX(YEAR) = 2006

Looks like it should work...