Skip to main content

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.


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
  5. Declare @vCounter Int
    Declare @vGetTblRowsSQL varchar(200)
    Declare Tbl_Cursor CURSOR For

    SELECT
    (N'INSERT ##TblRows Select ''' + [name] + ''' As TblName, Count(*) As Recs From ' + [name]) As GetTblRowsSQL
    FROM sys.[tables]

    Open Tbl_Cursor
    FETCH NEXT FROM Tbl_Cursor INTO @vGetTblRowsSQL
    Set @vCounter = 0
    While @@FETCH_STATUS = 0
    Begin
    if @vCounter = 0 Select 'Table Name ' As TblName, 9999999 As Recs INTO ##TblRows
    EXEC(@vGetTblRowsSQL)

    FETCH NEXT FROM Tbl_Cursor INTO @vGetTblRowsSQL
    Set @vCounter = @vCounter + 1
    End

    Delete From ##TblRows Where TblName Like 'Table Name%'

    Select * From ##TblRows Order By TblName
    Select Sum(Recs) As TotalRecs From ##TblRows

    CLOSE Tbl_Cursor
    DEALLOCATE Tbl_Cursor

    Drop Table ##TblRows

    ReplyDelete
    Replies
    1. This will work against your entire DB unless you provide a Where clause when retrieving the table names.

      Note: For the [If @vCounter = 0...] line, you should add spaces to 'Table Name ' to have as many characters as your largest table name to avoid an error.

      level3looper

      Delete
    2. Thanks for the comment - when I get chance I'll have a look at your solution

      Delete

Post a Comment

Popular posts from this blog

Always Encrypted

By David Postlethwaite

Always Encrypted is new features in SQL Server 2016 and it is also available in Azure SQL Database. Here you can encrypt columns in a table with a master key and a certificate so that they will appear as encrypted strings to those who don’t have the required certificate installed on their pc.
Once the certificate is installed on the computer then the unencrypted data can then be seen as normal.

The data passes from database to your application as the encrypted value, only the application with the correct certificate can unencrypt the data so it is secure across the wire. This will go some way to resolving the concern of people worried about putting their sensitive data on a shared server in the cloud such as Microsoft Azure and accessing the data across the Internet.

At the time of writing Always Encrypted is only supported with ADO.NET 4.6, JDBC 6.0 and ODBC 13.1 but expect other driver to become available.

The calling application (including SSMS) must also hav…

SQL Server 2012 and Virtual Service Accounts

This post is written by David Postlethwaite
If you are using SQL Server 2012 you will probably have noticed that the default account for the SQL services has changed from that used in previous versions. With SQL 2005 and 2008 the default account for SQL service and SQL Agent service was “NT Authority\System”. This is one the built in accounts on a Windows machine, managed by the machine and selectable from a dedicated dropdown list

The Network Service account was introduced in Windows 2003 as an alternative to using the LocalSystem account, which has full local system privileges on the local machine, a major security concern.
The Network Service has limited local privileges easing these security concerns but when many services on a machine use the Network Service account it becomes harder to track which service is actually accessing resources and performing actions, because all the services are using the one Network Service account.
Also, this account, by default, has sysadmin per…

SQL Server Express v SQL Server Developer Edition

SQL Server Express v  SQL Server Developer Edition
Over the weekend I received the following in an email from Ali Ahmad who asked me some questions about learning SQL Server. We exchanged a couple of emails so I have boiled this down to the most salient points.

Question:
Goal: I want to learn SQL Server inside out… for career progression as DBA/BI/data mining. I'm a data analyst and want to learn inside out about data warehousing.
•I understand relational database concepts...
•I have SQL Server 2014 express installed…
•I need to download the adventure works sample in order to play with it.
•So much knowledge on Microsoft website it's easy to get lost… where do I begin?

SQL Server Express v SQL Server Developer
This is interesting. If you want to learn SQL Server inside out including the Business Intelligence suite of applications I would suggest downloading the developer edition of SQL Server which since 2016 has been made available free of charge. Prior to that there was a f…