Even this simpliest query has the same result. There is the foreign key from TradeActive.IDPosition that references Position.ID.
What can be a reason for this estsimates?

I even have the index on the IDPosition column which statistics was updated yesterday. I rebuild it but nothing has changed in estimates besides of the index used to scan.

Thanks for explaining.

Here are the plans, sorry for misunderstanding.

Here are the screenshots. Tell me if you prefer other format.

GilaMonster, to judge by IO reads and execution plan estimated cost the better is obviously the second. But optimizer chooses the first and this was the question.

The Dixie Flatline, the concept of "good enough" solution makes sense.

Sergiy, execution time is not the constant. I run query 5 times with Set Statistics IO,TIME On and here is the result:

SQL Server Execution Times:

CPU time...

Yes, in this respect it matters and makes sense 'cause the subquery (Select ABS(CheckSUM(NEWID()) / 100000000)) is nondeterministic.

NewID() doesn't matter in this case

I rewrote it like

Select

Coalesce ((Select Value From Test Where ID = -2), -1)

or even

Select

Case

When

(Select Value From Test Where ID = -2) is...

Yeah, you are right 🙂

You're welcome 🙂

Link's fixed

Try something like this:

Select

tl.team,

Sum (wins.ats) as top5

From

dbo.teamLogs tl

Outer Apply

(

Select

Top 5 tl1.ats

From

dbo.teamLogs tl1

Where

tl1.team = tl.team

Order By

tl1.gamedate DESC

) as wins

Group by

tl.team