What are different ways to get total number of rows in a table.?

Problem: What are different ways to get total number of rows in a table.?

Solution:

A Table stores data in the format of columns and rows. Every Table stores the total number of rows in different aspect. There are few different ways to retrieve the number of rows in a table.

When you query the table directly for the total number of rows there will be Some I/O cost on the table as there will be an index scan or table scan operation performance operation in the execution, to avoid that there are few alternatives. Let’s deep dive into that.

·         Space utilization System Stored Procedure - sp_spaceused

Displays the number of rows, disk space reserved, and disk space used by a table, indexed view, or Service Broker queue in the current database, or displays the disk space reserved and used by the whole database.

·         Partitions - sys.partitions

Contains a row for each partition of all the tables and most types of indexes in the database. Special index types such as Full-Text, Spatial, and XML are not included in this view. All tables and indexes in SQL Server contain at least one partition, whether they are explicitly partitioned.     

·         Indexes - sys.indexes

Contains a row per index or heap of a tabular object, such as a table, view, or table-valued function.

·         SysIndexes - sys.sysindexes

Contains one row for each index and table in the current database. XML indexes are not supported in this view. Partitioned tables and indexes are not fully supported in this view; use the sys.indexes catalog view instead.

·         Partition Stats - sys.dm_db_partition_stats

Returns page and row-count information for every partition in the current database.

Some of this information might not be accurate because these are based on the updates that SQL server writes to these views.

Example:

In this example, I would like to show all possible ways to get the total number of rows in a table.

--1. Querying directly the table

select Count(1) as Rows from dbo.Employees

OUTPUT:

Rows

2826203

--2. System Stored Procedure

EXEC sp_spaceused 'dbo.employees'

OUTPUT:

name	rows	reserved	data	index_size	unused
Employees	2826203	92632 KB	92296 KB	208 KB	128 KB

--3. Partitions

select SUM(rows) as total_count from sys.partitions p

INNER JOIN sys.tables t ON t.object_id=p.object_id

where t.is_ms_shipped=0

and  object_name(t.object_id)= 'Employees'

and schema_name(t.schema_id)='dbo'

and p.index_id=1

OUTPUT:

total_count

2826203

--4. Partitions + Tables+ Indexes

SELECT TBL.object_id, TBL.name, SUM(PART.rows) AS rows

FROM sys.tables TBL

INNER JOIN sys.partitions PART ON TBL.object_id = PART.object_id

INNER JOIN sys.indexes IDX ON PART.object_id = IDX.object_id

AND PART.index_id = IDX.index_id

WHERE TBL.name = 'Employees'

AND IDX.index_id < 2

GROUP BY TBL.object_id, TBL.name;

OUTPUT:

object_id	name	rows
542624976	Employees	2826203

--5.Indexes( This particular view is not supported in future version starting 2016)

select rows as total from sysindexes

where id = OBJECT_ID('[dbo].[Employees]') and indid < 2

OUTPUT:

total

2826203

--6.Partition Statistics

SELECT OBJECT_NAME(object_id), SUM(row_count) AS rows

FROM sys.dm_db_partition_stats

WHERE object_id = OBJECT_ID('[dbo].[Employees]')

AND index_id < 2

GROUP BY OBJECT_NAME(object_id);

OUTPUT:

table_name	rows
Employees	2826203

Now as we can see almost all the queries above have the same results. So, let me compare the Execution costs from all these batch queries at a single grid to see how much of a variance it is having. I used xml of the execution plan to compare the batch query costs and statements total cost, please see the below screenshot for comparison results.

 OUTPUT: