Hey Carl - sounds like a good solution to me dude.

Phil

Yes, it SCANS the NC index on Orders.CustomerID.   If I remove the TOP 2, then it uses an index SEEK on OrderDetails.ProductID.

I can see how this might be beneficial -...

Just to reinforce the point,  BOL states the following:

"If a SELECT statement that includes TOP also has an ORDER BY clause, the rows to be returned are selected from the...

Still see the same behaviour on SP4

Version is:

Index,  Name,            Internal_Value,  Character_Value

2,      ProductVersion,  524288,          8.00.760

For what it's worth, I've included the stats output too. I've ordered the stats output by date.  The columns with...

Nothing. Sorry - I posted it by mistake before I was ready.  I've just spent best part of an hour filling all the rest in as an EDIT, and the...

Serqiy,

Craig's got it all covered here.  By issuing a ROLLBACK, he ensures that the entire current transaction is rolled back (as Kenneth says, not just the current nesting level), which...

I think I may have an explanation for this now.

When writing 

select bit_col from table where bit_col = 1

the value of 1 is an integer (because we haven't said...

Andrew,

Sorry, but you're making my point for me.  The optimizer SHOULD know that there are only a few 0's.  That's what statistics are for.  There's nothing wrong with having an...

That's a very good spot indeed, Otto!  I've made the change, and it does indeed now do an index seek, requiring only 3 logical reads.  Just what I thought it...

I'd agree with you if those two values were equally distributed (e.g. M and F  for Male and Female).  However, in our case, we KNOW that the number of records...

I instinctively avoid using optimizer hints for indexes, as the optimizer generally gets things right (there are a few rare occasions when it doesn't, but on the whole I'd trust...

This is probably not your main issue,  but I recommend it in any case.  If you're not already doing so, ensure that all your jobs specify "SET NOCOUNT ON" as...