Count a Particular Value Across All Columns in SQL Server

To count a particular value across all columns, turn each row’s columns into a list and count the matches. Four T-SQL methods do that, and they don’t cost the same.

A Real Question: Where Does This Email Appear?

The question comes from a performance tuning workshop. A staff member’s email address has to change for technical reasons. Before the change, someone must know every place the old address appears. In a table with several email columns, someone must count a particular value across all of them.

The demo uses a small staff table with three email columns. The first script creates a database named ValueCountDemo for this post only. Maya’s address sits in all three columns of Maya’s row. It also appears once in Leo’s row and once in Priya’s.

IF DB_ID(N'ValueCountDemo') IS NULL CREATE DATABASE ValueCountDemo;
GO
USE ValueCountDemo;
GO
DROP TABLE IF EXISTS dbo.StaffContacts;
CREATE TABLE dbo.StaffContacts (
    StaffID       int IDENTITY(1,1) NOT NULL PRIMARY KEY,
    FullName      nvarchar(60)  NOT NULL,
    WorkEmail     nvarchar(100) NULL,
    PersonalEmail nvarchar(100) NULL,
    BackupEmail   nvarchar(100) NULL
);
INSERT INTO dbo.StaffContacts (FullName, WorkEmail, PersonalEmail, BackupEmail)
VALUES (N'Maya Collins', N'maya@bakery.example', N'maya@bakery.example', N'maya@bakery.example'),
       (N'Leo Brennan', N'leo@bakery.example', N'maya@bakery.example', NULL),
       (N'Priya Shah', N'priya@bakery.example', NULL, N'maya@bakery.example'),
       (N'Sam Rivera', N'sam@bakery.example', N'sam.r@mail.example', N'sam@bakery.example');

The expected answer is 5: three in Maya’s row, one in Leo’s and one in Priya’s. The first method counts with CASE. Each CASE gives 1 when its column matches and 0 when it doesn’t. Adding the three CASE results gives the matches per row, and SUM adds the rows.

SELECT SUM(
         CASE WHEN WorkEmail = N'maya@bakery.example' THEN 1 ELSE 0 END
       + CASE WHEN PersonalEmail = N'maya@bakery.example' THEN 1 ELSE 0 END
       + CASE WHEN BackupEmail = N'maya@bakery.example' THEN 1 ELSE 0 END) AS Occurrences
FROM dbo.StaffContacts;

Count a Particular Value With CROSS APPLY

The CASE sum repeats the value once per column. With 30 columns, that is 30 lines to edit. A neater form lists the columns once. CROSS APPLY with a VALUES list turns each row’s three columns into three small rows. A plain COUNT with a WHERE filter then finishes the job.

SELECT COUNT(*) AS Occurrences
FROM dbo.StaffContacts AS s
CROSS APPLY (VALUES (s.WorkEmail), (s.PersonalEmail), (s.BackupEmail)) AS v (Email)
WHERE v.Email = N'maya@bakery.example';

The result is 5 again. The value appears once in the query, which makes it easy to change. The same shape tells you which column holds each match. Add the column name to the VALUES list and group by it.

SELECT v.ColumnName, COUNT(*) AS Occurrences
FROM dbo.StaffContacts AS s
CROSS APPLY (VALUES (N'WorkEmail', s.WorkEmail), (N'PersonalEmail', s.PersonalEmail), (N'BackupEmail', s.BackupEmail)) AS v (ColumnName, Email)
WHERE v.Email = N'maya@bakery.example'
GROUP BY v.ColumnName
ORDER BY v.ColumnName;

Now you know where to update. The same shape also finds NULL cells, if you change the filter to test for NULL.

SELECT COUNT(*) AS EmptyCells
FROM dbo.StaffContacts AS s
CROSS APPLY (VALUES (s.WorkEmail), (s.PersonalEmail), (s.BackupEmail)) AS v (Email)
WHERE v.Email IS NULL;

Two cells are empty: Leo’s backup address and Priya’s personal address.

UNPIVOT and UNION ALL

UNPIVOT turns columns into rows as well. It’s built for this job. It needs every unpivoted column to have the same data type, and it drops rows whose value is NULL. So don’t use it to count NULLs. UNION ALL is the old method. It stacks one query per column, and it works on any version.

SELECT COUNT(*) AS Occurrences
FROM dbo.StaffContacts
UNPIVOT (Email FOR SourceColumn IN (WorkEmail, PersonalEmail, BackupEmail)) AS u
WHERE u.Email = N'maya@bakery.example';
GO
SELECT COUNT(*) AS Occurrences
FROM (SELECT WorkEmail AS Email FROM dbo.StaffContacts
      UNION ALL SELECT PersonalEmail FROM dbo.StaffContacts
      UNION ALL SELECT BackupEmail FROM dbo.StaffContacts) AS t
WHERE t.Email = N'maya@bakery.example';

Both return 5. One detail matters in the UNION ALL version. Count the rows with COUNT, and don’t add the values with SUM. The next script uses the three-column numeric sample from the original question, with the digit 1 in seven places. The CASE sum returns 7. A SUM over the digit 2, which appears once, returns 2 and not 1.

DROP TABLE IF EXISTS dbo.Scores;
CREATE TABLE dbo.Scores (Col1 int, Col2 int, Col3 int);
INSERT dbo.Scores VALUES (1, 1, 1), (1, 2, 1), (1, 1, 3);
SELECT SUM(CASE WHEN Col1 = 1 THEN 1 ELSE 0 END + CASE WHEN Col2 = 1 THEN 1 ELSE 0 END + CASE WHEN Col3 = 1 THEN 1 ELSE 0 END) AS Ones FROM dbo.Scores;
SELECT SUM(a) AS WrongForTwo
FROM (SELECT Col1 AS a FROM dbo.Scores WHERE Col1 = 2
      UNION ALL SELECT Col2 FROM dbo.Scores WHERE Col2 = 2
      UNION ALL SELECT Col3 FROM dbo.Scores WHERE Col3 = 2) AS t;

Which Method Costs Less in Reads

On four rows, all four methods look the same. The difference shows on a bigger table. The script below builds a table of 200,000 rows and runs the four methods in turn. STATISTICS IO prints the pages each one reads.

SET NOCOUNT ON;
DROP TABLE IF EXISTS dbo.StaffLarge;
CREATE TABLE dbo.StaffLarge (StaffID int IDENTITY(1,1) PRIMARY KEY, WorkEmail nvarchar(100) NULL, PersonalEmail nvarchar(100) NULL, BackupEmail nvarchar(100) NULL);
INSERT INTO dbo.StaffLarge (WorkEmail, PersonalEmail, BackupEmail)
SELECT N'w' + CAST(n AS nvarchar(10)) + N'@bakery.example',
       CASE WHEN n % 10 = 0 THEN N'maya@bakery.example' ELSE N'p' + CAST(n AS nvarchar(10)) + N'@mail.example' END,
       CASE WHEN n % 25 = 0 THEN N'maya@bakery.example' END
FROM (SELECT TOP (200000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n
      FROM sys.all_objects AS a CROSS JOIN sys.all_objects AS b) AS x;
GO
SET STATISTICS IO ON;
SELECT SUM(CASE WHEN WorkEmail = N'maya@bakery.example' THEN 1 ELSE 0 END + CASE WHEN PersonalEmail = N'maya@bakery.example' THEN 1 ELSE 0 END + CASE WHEN BackupEmail = N'maya@bakery.example' THEN 1 ELSE 0 END) AS CaseSum FROM dbo.StaffLarge;
SELECT COUNT(*) AS CrossApplyCount FROM dbo.StaffLarge AS s CROSS APPLY (VALUES (s.WorkEmail), (s.PersonalEmail), (s.BackupEmail)) AS v (Email) WHERE v.Email = N'maya@bakery.example';
SELECT COUNT(*) AS UnpivotCount FROM dbo.StaffLarge UNPIVOT (Email FOR SourceColumn IN (WorkEmail, PersonalEmail, BackupEmail)) AS u WHERE u.Email = N'maya@bakery.example';
SELECT COUNT(*) AS UnionAllCount FROM (SELECT WorkEmail AS Email FROM dbo.StaffLarge UNION ALL SELECT PersonalEmail FROM dbo.StaffLarge UNION ALL SELECT BackupEmail FROM dbo.StaffLarge) AS t WHERE t.Email = N'maya@bakery.example';
SELECT (SELECT COUNT(*) FROM dbo.StaffLarge WHERE WorkEmail = N'maya@bakery.example') + (SELECT COUNT(*) FROM dbo.StaffLarge WHERE PersonalEmail = N'maya@bakery.example') + (SELECT COUNT(*) FROM dbo.StaffLarge WHERE BackupEmail = N'maya@bakery.example') AS SubqueryCount;
SET STATISTICS IO OFF;

All four agree on 28,000. The first three methods read the table once. UNION ALL reads it three times, once for each column, and that costs three times the pages. Adding three separate COUNT subqueries does the same. The numbers compare reads, not CPU. Choose CROSS APPLY by default. It reads once, it is short, and it also finds NULLs.

When the Table Has Many Columns

A table with 40 text columns makes a long VALUES list. Let the catalog write it. The script below builds the list with STRING_AGG, which needs SQL Server 2017 or later. It takes every nvarchar and varchar column of the table. It puts the list into a CROSS APPLY statement and runs it with sp_executesql. The value travels as a parameter. QUOTENAME protects the column names. A quote in either one can’t break the statement.

DECLARE @list nvarchar(max), @sql nvarchar(max);
SELECT @list = STRING_AGG(N'(N' + QUOTENAME(c.name, '''') + N', s.' + QUOTENAME(c.name) + N')', N', ') WITHIN GROUP (ORDER BY c.column_id)
FROM sys.columns AS c
JOIN sys.types AS t ON t.user_type_id = c.user_type_id
WHERE c.object_id = OBJECT_ID(N'dbo.StaffContacts') AND t.name IN (N'nvarchar', N'varchar');
SET @sql = N'SELECT COUNT(*) AS Occurrences FROM dbo.StaffContacts AS s CROSS APPLY (VALUES ' + @list + N') AS v (ColumnName, Email) WHERE v.Email = @target;';
PRINT @sql;
EXEC sys.sp_executesql @sql, N'@target nvarchar(100)', @target = N'maya@bakery.example';

The statement that SQL Server runs looks like this. It is output, not code to run.

SELECT COUNT(*) AS Occurrences FROM dbo.StaffContacts AS s CROSS APPLY (VALUES (N'FullName', s.[FullName]), (N'WorkEmail', s.[WorkEmail]), (N'PersonalEmail', s.[PersonalEmail]), (N'BackupEmail', s.[BackupEmail])) AS v (ColumnName, Email) WHERE v.Email = @target;

The result is 5, the same as before. FullName joined the list, because it is a text column too, and no name equals the address. Add a filter on the column names if you only want the email columns.

Is the Single Table Enough?

You could argue that this answers only half of the workshop question. The staff member’s address could live in other tables too. That’s right: this script counts inside one table. To search a whole database, run the generated statement once per table, and decide first which tables are in scope. A search that touches every column of every table reads the whole database.

What to Remember

To count a particular value across all columns, turn the columns into rows and count the rows that match. CROSS APPLY with VALUES is my default. It reads the table once and handles NULLs. Count with COUNT, not SUM, so the answer doesn’t depend on the value you search for.

Avoid UNION ALL on big tables, because it reads the table once for each column. When you finish the demo, run the cleanup script to drop the database.

USE master;
GO
IF DB_ID(N'ValueCountDemo') IS NOT NULL
BEGIN
    ALTER DATABASE ValueCountDemo SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE ValueCountDemo;
END;

A column is not a boundary, it is one more place the same value can hide.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.