Thanks.

Ok so now it looks like we down to the Properties table. I did notice another lookup popout there, I'm sure you know how to remove that now, but it...

nice one.

Ok could we get a new plan please.

could you also confirm that the stats are up to date?

It's an option for the Property index I'm just worried about the impact of creating a LARGE index that is so wide just for this query...

Might be worth trying to...

Ok the other thing is are your stats up to date?

I've noticed most of your estimated vs actual row counts vary largely, I'm not if this is stats out of...

What type of field is ImageUrl?

I think this should be added to your Property image covering index that should get rid of the Key Loopup for that table.

WOW Great post.

Most people don't take the time to give as much info as you have thanks for that.

For the execution plan, could you post the actual plan as a...

Hi Sergiy,

The index that I created to test the OP's problem definitely has a field in postion 1 that is of type 'BIT'.

I do agree with you I would probably...

Gail am I correcting in saying and I think I might have learn't or if I am wrong missunderstood, but ify ou have two columns in your where clause and...

Joseph Fallon what order are the fields in your in covering index, this will make a difference.

The other thing to look out for is that I'm pretty sure, but happy...

As mentioned, please supply more information in the form of table structures queries and execution plans.

Hi,

The first thing to remember is that the estimate execution plan is "estimated" and not "actual", so you can't trust it to be what will actually happen.

Second the %'s from...

Have you tried this?

SELECT *

FROM

(

SELECT ROW_NUMBER() OVER (ORDER BY GETDATE())as NUM, Id FROM @tbl

) p

PIVOT (MIN(ID) FOR NUM IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10])) PVT

You can just use the same sample data...

Thanks Barry, I wasn't aware of that 🙂

Learn't something new for the day .

could you give me an example of two tables with a few columns from each table and then the relationship for those tables in a create script and then I...