Multiple valid OR conditions in select statement SQL Server

If you have a query that has OR condition,  you will not get multiple rows just because there are multiple conditions satisfied the result.
However many rows exist in table that many rows will be returned.

Also all the operators are evaluated before the query result is returned.
This can result in multiple rows to be returned.

Lets work with an example:

Create table #person (name varchar(50), age int)

-- insert one row

insert into #person values ('tom', 40)

-- select person whose name is either 'tom' or age is 40.

select * from #person 

where name = 'tom' or age = 40

One row will be returned.

-- insert second row 

insert into #person values ('monica', 41)

-- Now select with multiple conditions 

select * from #person 

where (name = 'tom' or age = 40 or age > 40)

2 rows will be returned which tells all the ORs are evaluated and based on filters, rows will be returned.

HTH