For example, I have 2 tables, which I need for my query,
Move for history of moving properties.
I must create a query which will return all properties + 1 additional boolean column,
IsInService, which will have value
true, in cases, when
Move table has a record for property with
DateTo = null and
MoveTypeID = 1 ("In service").
I have created this query:
WHERE PropertyID = p.ID
AND DateTo IS NULL
AND MoveTypeID = 1) AS IsInService
[Property] as p
OFFSET 100500 ROWS FETCH NEXT 50 ROWS ONLY;
I'm not so strong in SQL, but as I know, subqueries are the evil :)
How to create high performance SQL query in my case, if it is expected that these tables will include millions of records?