Optimized way to check individual values ​​in a column

Question

I am looking for an answer, where from a column (table of about 50 thousand records) I just need to check if the column has at least two different values.

Select Count(Distinct ColumnA) from tableX 

The above query gives me the result, but it is not optimized, since I just want to get an answer in Boolean

i.e. Count Greater than 1 or Not?

In addition, increasing the number of records in table X will degrade query performance, I am looking for something that does not depend on the number of records in the table. Any help is appreciated :) thanks.

Answer 1

 SELECT CASE WHEN COUNT(*) = 2 THEN 'Yes' ELSE 'No' END FROM (SELECT DISTINCT TOP 2 ColumnA FROM tableX WHERE ColumnA IS NOT NULL) T 

Most likely, you will get a plan that stops processing after the first two DISTINCT values ​​are found.

Be sure to use a thread aggregate or HASH MATCH (FLOW DISTINCT) in the execution plan, as both of them are not blocked. If DISTINCT implemented as a HASH MATCH without (FLOW DISTINCT) , then this is blocked and all rows will still be processed.

eg. Both of these plans stop without processing all the lines.