Introduction
When we want to get the row count of a table, we use the transact-SQL function count (*). In the case of large table which may contain millions of rows, it can take a long time to return the row count of the whole table, and that leads to slow performance for the query.
Count () function
Every DBA know how to use count (*) and know how suffering the performance. SQL server makes a full index/ table scan to return the total table’s row count. There was advice for the DBA to avoid using the aggregate function count () for the whole table because it affects the database performance. When the following query is executed in query analyzer for the demo database AdventureWorks:
use AdventureWorks
go
select count (*) from Sales.SalesOrderDetail
The Query analyzer returns 121317 rows.
When we click the icon “display estimated execution plan” in the toolbar of the management studio for SQL server 2005, we get the following graph:
As we see in the figure from right to left, the following actions are applied:
- Index scan for the whole table, which is time consuming process.
- Apply stream aggregate in the next step.
The New Method row_count ()
The information included in Object Catalog Views of SQL Server 2005: sys.partitions and sys.allocation_units are used to get the count of the rows of the whole table. This function can be used in SQL Server 2005.
sys.partitions View
sys.partitions View contains a row for each partition of all the tables and indexes in the database. All tables and indexes in SQL Server 2005 contain at least one partition in that view, even if they are not explicitly partitioned.
The View includes the following fields which are used in the new method:
|
Field Name |
Data Type |
Description |
| partition_id | bigint | ID of the partition. Is unique within a database. |
| object_id | int | ID of the table to which this partition belongs. Every table is composed from one partition at least. |
| index_id | ID of the index within the object to which this partition belongs. 0 : heap table 1: with clustered index |
|
| rows | bigint | number of rows in the partition. |
sys.allocation_units
The sys.allocation_units view contains a row for each allocation unit in the database.
The View includes the following fields which are used in the new method:
|
Field Name |
Data Type |
Description |
| container_id | bigint | The container_id = sys.partitions.partition_id. |
| Type | tinyint | Type of allocation unit: 0 = Dropped 1 = In-row data (all data types, except LOB data types) 2 = Large object (LOB) data (text, ntext, image, xml) 3 = Row-overflow data |
In the new UDF row_count, [sys.partitions] view is joined with [sys.allocation_units] view. The filter of selection based on the following criteria:
- [sys.allocation_units].type = 1 which retrieve only the in-row data except the large object (LOB) of type like text, ntext, image.
- [sys.partitions].index_id in (0,1): 0 is the heap table , 1 : Clustered table
- [sys.partitions].rows is not null
The UDF row_count is executed in every database , and is given a permission public. The UDF row_count is described in the next section.