Blog

SQL - How can we count rows accurately?

With SQL Server, the Count and CountRow functions produce delegation warnings. Without these functions, does it make it impossible to count records from large SQL Server tables? The answer is no! In this post, I'll describe a trick that enables us to carry out this task.

Counting records should be a simple task but in practice, it can be more difficult than we first imagine. The reason for this is that all the count related operation are not delegable, meaning that they operate against the first 2,000 records (at most) in a table only.
 

How can we workaround this limitation? In this post, I’ll describe one technique that we can use.

Example Scenario

To demonstrate this problem, let's take the example of a table of issues. There are 2,500 rows in this table and there are three columns of particular interest - Description, CreateDateTime, and CloseDateTime.


Our requirement is to count the number of records that were closed in the past 7 days - a typical feature that users would reasonably ask for.

Verifying the method that does NOT work

The logical method that most app builders would approach this is add a label, and to set the Text property to the following formula:

CountRows(Filter('[dbo].[Issue]',
                 CloseDateTime > DateAdd(Today(), -7, Days)
          )
)
As we expect, this method will not work accurately, and the error in the screenshot beneath confirms this - CountRows is not a delegable function.
 

Hopefully, we can see the practical problem that this limitation presents. It's reasonable to want to return counts based on filters that could include issue type, open and close dates, the property that the issue relates to, etc, etc. The abscense of delegable support for the count operators makes it difficult to carry out such tasks.

How to work around this limitation/delegation warning

As with most data related SQL problems,  the go-to workaround is to build a view. We can simply build a view that calls the SQL Count function to return a single row/single column result set that contains the result.

Alternatively, we can use a SQL technique that I and many database developers traditionally use.The rationale behind this technique is to build a view with a column that returns the numeric value 1 against each row. That way, we can sum our target rows in order to produce an accurante count.

To demonstrate, here's the definition of our example view.

CREATE VIEW IssueView
AS
SELECT
     IssueID,    
     TenantID,
     PropertyID,
     [Description],
     IsEmergency,
     CreateDateTime,
     CloseDateTime, 
     1 AS [One]
FROM dbo.Issue

Here’s how our data looks when we select the view from a query in Management Studio.
 
From Power Apps, we can now add our view and modify our formula as follows:

Sum(Filter('[dbo].[IssueView]', 
           CloseDateTime > DateAdd(Today(), -7, Days)), 
    One
 )

This formula now returns an accurate count because the Sum function is delegable, whereas the CountRows, Count, and CountA functions are not.

The beauty of this technqiue is that because we expose other columns in our view, we can further filter the records that we want to count by including extra conditions in our call to the Filter function.

Verifying the Results

Let's now verify that this technique works by carrying out some quick tests in Management Studio. From our 2,500 row table, there are 54 records that match property ID 35. I can confirm there are several matching records that are beyond the limit of row 2,000.


The label in our app that calls the Sum function, returns the identical record count.
 

Conclusion

Counting rows can be difficult because the count functions are not delegable. One way to workaround this problem is to build a view and to append a column that returns the numeric value one. This enables us to effectively count rows by calling the sum function instead, which is delegable.
Previous
Next
Related posts