Rafael, there are several factors that determine the performance of a SELECT construct. Immediately, some comments on the two constructions that published in this topic, which, to facilitate the comments, I referenced as
(a) SELECT * FROM CLIENTS C WHERE C.FL_EXCLUDED IS NULL OR C.FL_EXCLUDED = 0;
(b) SELECT * FROM CUSTOMERS C WHERE ISNULL (C.FL_EXCLUDED, 0) = 0;
(1) In construction (a), the WHERE clause contains the logical OR operator. Usually, restrictions of type
WHERE expressão_lógica1 OR expressão_lógica2 OR ...
force a sequential scan, either through index or directly into table . There are exceptions.
(2) In construction (b) the WHERE clause contains function call having table column as parameter. This usually prevents SQL Server from finding the appropriate index (if it exists) because the restriction becomes non- sargable , ( index scan ) or directly in the table ( table scan ). There are exceptions here too.
(3) Another factor that can influence performance is what contains the column_list of the SELECT clause. Constructs of type
SELECT *
may make the choice of nonclustered index unfeasible, especially if it is not coverage. This involves including the presence or not of the Key Lookup ( Key Lookup ) in the execution plan.
(4) Assuming the purpose of the query is to get only the customer ID, then we could have
(c)
SELECT idCliente FROM CLIENTES C WHERE C.FL_EXCLUIDO IS NULL OR C.FL_EXCLUIDO = 'false';
But the construction
(d)
SELECT idCliente FROM CLIENTES C WHERE C.FL_EXCLUIDO IS NULL
union all
SELECT idCliente FROM CLIENTES C WHERE C.FL_EXCLUIDO = 'false';
can be more efficient than construction (c) if there are filtered indexes:
CREATE nonclustered INDEX I3_CLIENTES on CLIENTES(FL_EXCLUIDO) where FL_EXCLUIDO = 'false';
CREATE nonclustered INDEX I4_CLIENTES on CLIENTES(FL_EXCLUIDO) where FL_EXCLUIDO is null;
Of course it also depends on the volume of data.
(5) I could list a number of other factors here. However, in constructions (a) and (b) it seems to me that sequential scanning will always occur ( scan ).
(6) The valid values for the FL_EXCLUDED column are 'true' and 'false', plus no information (NULL). That is, construction (a) could be rewritten as
(e)
SELECT * from CLIENTES
except
SELECT * from CLIENTES where FL_EXCLUIDO = 'true';
The efficiency (or otherwise) of this solution depends on the characteristics of the FL_EXCLUDED column and the existing indexes.