SQL Server why the index is not used with OR

Question

I study indexes and try to understand how they work, and how I can use them to improve performance, but I'm missing something.

I have the following table:

Person

| Id | Name | Email | Phone |
| 1  | John |  E1   |  P1   |
| 2  | Max  |  E2   |  P2   |

I am trying to find a better way to index columns Emailand Phoneconsidering that the queries will (most of the time) look like

[1] SELECT * FROM Person WHERE Email = '...' OR Phone = '...'
[2] SELECT * FROM Person WHERE Email = ...
[3] SELECT * FROM Person WHERE Phone = ...

I thought the best approach is to create a single index using both columns:

CREATE NONCLUSTERED INDEX [IX_EmailPhone]
ON [dbo].[Person]([Email], [PhoneNumber]);

However, with the above index, only the query [2] benefits from the index search, while others use index scanning.

I also tried to create several indexes: one with both columns, one for email and one for email. In this case, [2] and [3] use the search, but [1] continues to use the scan.

? ?

Answer 1

. , / , , .

, -

• j = 123.
  k = 456.

• j = 123.
  k = 456.

• j = 123.
  k = 456.

, .
, .

---

;with t(n) as (select 0 union all select n+1 from t where n < 999)

select      1+t0.n+1000*t1.n                                as i
           ,floor(rand(cast (newid() as varbinary))*1000)   as j
           ,floor(rand(cast (newid() as varbinary))*1000)   as k 

into        t

from        t t0,t t1 

option       (maxrecursion 0)
;

create index t_j on t (j);
create index t_k on t (k);

update statistics t (t_j)
update statistics t (t_k)

---

select      *
from        t (forcescan)
where       j = 123
        or  k = 456

• .

Seek

select      *
from        t (forceseek)
where       j = 123
        or  k = 456

• " " . (123 456).
• " " : ( ) ( UNION ALL)
• " " :
• "Rid Lookup" + " " . (t)

Answer 2

, (email) (phone, email).

OR . AND, OR, ( , phone ).

:

SELECT *
FROM Person 
WHERE Email = '...' 
UNION ALL
SELECT *
FROM Person 
WHERE Email <> '...' AND Phone = '...';

SQL Server .