Thin T-SQL counter request ... can it be optimized with EXISTS or something else?

Question

Is there a way to make this query faster (which means less read / write I / O requires less SQL server). The logic is essentially this:

• I am calculating various values ​​from a column
• if there is no more than one single value, it is considered existing
• A list is created with the column name and 1 or 0 if it exists

I would like to do something with EXISTS (in t-sql, which completes the table / index check if SQL Server finds a match with the EXISTS predicate). I am not sure if this is possible in this query.

Note. I am not looking for answers, as there is an index on the table ... besides :)

with SomeCTE as ( select count(distinct(ColumnA)) as ColumnA, count(distinct(ColumnB)) as ColumnB, count(distinct(ColumnC)) as ColumnC from VERYLARGETABLE ) select 'NameOfColumnA', case when ColumnA > 1 then 1 else 0 end from SomeCTE UNION ALL select 'NameOfColumnB', case when ColumnB > 1 then 1 else 0 end from SomeCTE UNION ALL select 'NameOfColumnC', case when ColumnC > 1 then 1 else 0 end from SomeCTE 

Just copy what I posted below in the comments. Therefore, after testing this solution. This makes requests run faster. To give two examples ... one request lasted from 50 seconds to 3 seconds. The other went from 9 + minutes (stopped to start it) and decreased to 1 min03sec. I also lack indexes (so according to DTA it should work 14% faster), I also run this in SQL Azure DB (where you are heavily throttled in terms of I / O, CPU memory and tempddb) ... very nice the solution is all around. One drawback is that min / max does not work with bit columns, but they can be converted.

Answer 1

If the column data type allows you to aggregate functions, and if there are indexes, one for each column, this will be fast:

 SELECT 'NameOfColumnA' AS ColumnName, CASE WHEN MIN(ColumnA) < MAX(ColumnA) THEN 1 ELSE 0 END AS result FROM VERYLARGETABLE UNION ALL SELECT 'NameOfColumnB', CASE WHEN MIN(ColumnB) < MAX(ColumnB) THEN 1 ELSE 0 END FROM VERYLARGETABLE UNION ALL SELECT 'NameOfColumnC' CASE WHEN MIN(ColumnC) < MAX(ColumnC) THEN 1 ELSE 0 END FROM VERYLARGETABLE ;