Sunday, 22 January 2017

How to Count the Total Number of Rows Across Multiple Tables

I have been working on SQL Server Consulting assignment,  a SQL Server 2014 migration for a new client over the past few weeks. When I’m undertaking such tasks I’m often asked additional questions that make my  day a little interesting. 

I got a question last week How to Count the Total  Number of Rows Across Multiple Tables. 

Lets look at a simple example

T1 has 13 rows
T2 has 12 rows
T3 has 12 rows

The scripts to create these tables and temporary tables in temp db is

CREATE TABLE #t1 ([c1] [nchar](10) NULL) ON [PRIMARY]

CREATE TABLE #t2 ([c1] [nchar](10) NULL) ON [PRIMARY]

CREATE TABLE #t3 ([c1] [nchar](10) NULL) ON [PRIMARY]

/*Insert Some data to the tables*/

DECLARE @i INT

SET @i = 1

WHILE @i & lt;= 13
BEGIN
 INSERT INTO #t1 (c1)
 VALUES ('x')

 SET @i = @i + 1
END

DECLARE @x INT

SET @x = 1

WHILE @x & lt;= 12
BEGIN
 INSERT INTO #t2 (c1)
 VALUES ('x')

 INSERT INTO #t3 (c1)
 VALUES ('x')

 SET @x = @x + 1
END

The questions seems like a simple one. We can use the aggregate functions in SQL to help do the maths in terms of counting the rows However the solution needs to two steps.

Like all things in SQL there is more than one way to achieve the same outcome this is the solution I came up and I’ve broken it down into separate parts.

Using the COUNT Aggregate function we can quickly  count the rows in one table. The first query counts all the rows in table t1

SELECT COUNT(*)
FROM #t1









So we have a starting point. Next we need count all the rows in the second table

SELECT COUNT(*)
FROM #t2

Will give you a count of all the rows in table2.

We need to repeat this for all the tables that we want to include in the final total row number.

When we have counted rows in all the necessary tables individually  we then need to combine the individual results into one result set.

We can do this with a UNION ALL. This will combine the counts for each table into a single result set.

The UNION ALL is important. A UNION does an implicit distinct so any tables with the same number of rows will result in the duplicates being removed. Compare the two results below


SELECT COUNT(*)
FROM #t1
UNION
SELECT COUNT(*)
FROM #t2
UNION
SELECT COUNT(*)

FROM #t3







Tables t2 and t3 each have 12 rows each. If we build our table row count table with a UNION it will do implicit distinct and remove duplicates. therefore we have two rows in the result set that have the value 12 so only one is included with the UNION. Therefore our next calculation of adding all the counts together will be wrong

UNION ALL does not do the implicit distinct duplicates remain in the final result. Which is what we want in this case.

SELECT COUNT(*)
FROM #t1
UNION ALL
SELECT COUNT(*)
FROM #t2
UNION ALL
SELECT COUNT(*)

FROM #t3











So we have a result that is giving us the count of the rows in each table. Now we need to add the row totals together. We can do that by putting our UNION ALL query into a derived table or common table expression (CTE). An inline view if you prefer. The CTE or derived table will allow us to include the result set of our UNION ALL statement in the FROM clause and the SELECT from it to sum the total rows. 

In this example I will use a CTE

;with cterc as
(SELECT COUNT(*) as rn
FROM #t1
UNION ALL
SELECT COUNT(*)
FROM #t2
UNION ALL
SELECT COUNT(*)
 FROM #t3)

 SELECT SUM(rn) as totalrowNo

from cterc








I use in SUM in the outer query to add the number of rows of each table in the final result set. Giving us 37 rows across the 3 tables t1, t2 and t3 

If you are looking to do this yourself. You need to do the following:

  • Use SELECT COUNT (*) on each table to have its rowed total
  • Use UNION ALL to build a result of the row count of each table
  • Wrap that result set in CTE or derived table
  • Select from the CTE or derived table SUMing the row count column

You can learn all about querying SQL Server on Learning Tree’s 534 Writing SQL Queries for Microsoft SQL Server course. If you would like attend the event running in London starting Feburary 7th either in class or online you can book a seat at a special price of £750 plus VAT. Email training@gethynellis.com for more details or to book your seat.


6 comments:

  1. As you say many ways to do this I've gone the Variable route just to replace your CTE, execution plans are similar but I'd be interested to see the them on a larger table set :

    DECLARE @Counter int

    Set @Counter = 0
    SELECT @Counter = COUNT(1)
    FROM #t1
    SELECT @Counter = @Counter + COUNT(1)
    FROM #t2
    SELECT @Counter = @Counter + COUNT(1)
    FROM #t3
    SELECT @Counter

    ReplyDelete
  2. Thanks for taking the time to comment. It would be good to compare on a larger data set, if I get a chance I'll knock up and example.

    ReplyDelete
  3. if you do not need the exact result at the moment, you could use

    SELECT
    s.name AS 'SchemaName'
    ,o.name AS 'TableName'
    ,SUM(p.row_count) AS 'RowCount'
    FROM sys.dm_db_partition_stats p
    JOIN sys.objects o ON o.object_id = p.object_id
    JOIN sys.schemas s ON o.schema_id = s.schema_id
    WHERE p.index_id <2 AND o.type = 'U'
    -- AND o.schema_id in (schema_id('dbo'))
    -- AND o.name in ('tbl1', 'tbl2', 'tbl3')

    it would be much faster (execution and coding, particularly, if you can use LIKE instead of IN).

    ReplyDelete
    Replies
    1. Thanks for the comment Thomas. In this case I did need the exact value. Thanks for the query though I'll check it out and maybe compare the performance of each.

      Delete
  4. Easy and I think quicker would be:

    SELECT (SELECT COUNT(*) FROM #t1) + (SELECT COUNT(*) FROM #t2) + (SELECT COUNT(*) FROM #t3)

    ReplyDelete
    Replies
    1. Yep that works for me - it has a slightly different query plan to my solution. Although with this small amount of data it doing the same number of reads. It would be interesting to see how both perform on a larger data set

      Delete