Counting DISTINCT over multiple columns

sql sql-server performance tsql query-optimization
Novitzky picture Novitzky · Sep 24, 2009 · Viewed 476.8k times · Source

Is there a better way of doing a query like this:

SELECT COUNT(*) 
FROM (SELECT DISTINCT DocumentId, DocumentSessionId
      FROM DocumentOutputItems) AS internalQuery

I need to count the number of distinct items from this table but the distinct is over two columns.

My query works fine but I was wondering if I can get the final result using just one query (without using a sub-query)

Answer

Jason Horner picture Jason Horner · Sep 26, 2009

If you are trying to improve performance, you could try creating a persisted computed column on either a hash or concatenated value of the two columns.

Once it is persisted, provided the column is deterministic and you are using "sane" database settings, it can be indexed and / or statistics can be created on it.

I believe a distinct count of the computed column would be equivalent to your query.

Related questions

When should I use cross apply over inner join?

What is the main purpose of using CROSS APPLY? I have read (vaguely, through posts on the Internet) that cross apply can be more efficient when selecting over large data sets if you are partitioning. (Paging comes to mind) I …

Read More
How do I obtain a Query Execution Plan in SQL Server?

In Microsoft SQL Server how can I get a query execution plan for a query / stored procedure?

Read More
SQL JOIN vs IN performance?

I have a case where using a JOIN or an IN will give me the correct results... Which typically has better performance and why? How much does it depend on what database server you are running? (FYI I am using …

Read More