sql server – Why does Index Seek Used When Order By Is Present

I have this query:

SELECT t.some_id AS id, t.* 
FROM some_table t
WHERE (t.first_col = '123'
OR t.second_cold = '321')
AND t.third_col != 'aaa'
AND t.some_date >= :someDate
AND t.some_date <= :someOtherDate
ORDER BY t.some_date

I have rewritten it to:

SELECT t.some_id AS id, t.*
FROM some_table t
JOIN (SELECT t.some_id, t.some_date
FROM some_table t
WHERE t.first_col= '123'
AND t.third_col != 'aaa'
AND t.some_date >= :someDate
AND t.some_date<= :someOtherDate
ORDER BY t.some_date
OFFSET 0 ROWS
UNION SELECT t.some_id, t.some_date
FROM some_table t
WHERE t.second_col = '321'
AND t.third_col != 'aaa'
AND t.some_date>= :someDate
AND t.some_date<= :someOtherDate
ORDER BY t.some_date
OFFSET 0 ROWS) AS filtered_table ON t.some_id = filtered_table.some_id 
ORDER BY t.some_date

and I have the following indexes:

CREATE NONCLUSTERED INDEX first_col_index ON some_table (
first_col,                                                                                         third_col,
some_date,                                                                                         some_id);

CREATE NONCLUSTERED INDEX second_col_index ON some_table (
second_col,                                                                                         third_col,
some_date,                                                                                         some_id);

and in the execution plan I can see the initial filtering is done with Index Seek operation. However, I only need the rows sorted in the final table, so if I eliminate the order by clauses from the inner queries:

SELECT t.some_id AS id, t.*
FROM some_table t
JOIN (SELECT t.some_id
FROM some_table t
WHERE t.first_col= '123'
AND t.third_col != 'aaa'
AND t.some_date >= :someDate
AND t.some_date<= :someOtherDate
UNION SELECT t.some_id
FROM some_table t
WHERE t.second_col = '321'
AND t.third_col != 'aaa'
AND t.some_date>= :someDate
AND t.some_date<= :someOtherDate) AS filtered_table ON t.some_id = filtered_table.some_id 
ORDER BY t.some_date

I’m now seeing two Clustered Index Scans with a simpler execution plan overall.

  1. Why are my indexes not enough to perform Index seek in the second query (without ordering)? Should I to make it perform Index Seek to have a faster read?
  2. Which query would be faster given that I have roughly 500K records in the table?
  3. Is there a better way to improve query performance (with different indexes or with any additional index to perform union operation faster, or rewriting my query)?