SQL Reloaded: Number of Records II (The DMV Approach)

    In SQL Server 2000 the safest way to return the total number of records from a table was to do a simple COUNT. I’m say the safest way because (for big tables) there was a faster but approximate way to obtain the same information by using sysindexes table, which, as its name denotes, was storing one record for each index and table in a given database. When I started to use SQL Server 2005 and further 2008 I naturally followed the same approach, from several reasons not attempting to find whether there is a better alternative. Yesterday, while scanning roughly the titles from MSDN blogs, my attention was caught by Martinjnh’s post SQL Server–HOW-TO: quickly retrieve accurate row count for table, in which, in addition to COUNT and sys.sysindexes view kept from compatibility reasons,  he gives 2 other alternatives that use two sys.partitions and sys.dm_db_partition_stats DMV (dynamic management views), that contain “a row for each partition of all the tables and most types of indexes in the database”, respectively the “page and row-count information for every partition in the current database”.

    Of course, I tested the queries and their accuracy by inserting/deleting a few records in one of the AdventureWorks tables. The “statistics” seems to be updated in real time, thing certified also by the SQL Server 2005 documentation. I wanted to see the definition of the respective views, though they are not browsable by using the Management Studio. Some time ago I discovered that the definition of system views, functions and stored procedures are available though the sys.all_sql_modules view, the union join between sys.sql_modules and sys.system_sql_modules views. Here’s the query I used:

-- retrieving the definition of system views used to return the number of records 
SELECT * 
FROM master.sys.all_sql_modules 
WHERE CHARINDEX ('sys.dm_db_partition_stats', definition)>0 
      OR CHARINDEX ('sys.sysindexes', definition)>0 
       OR CHARINDEX ('sys.partitions', definition)>0 

Notes:
1.   Another way to get the definition of an object is to use the Object_Definition function:

SELECT object_definition(object_id('sys.sysindexes')) 

2.   Following Mladen Prajdic’s post, an SQL Server MVP, I found out that all the system objects are stored in the (hidden) read-only MsSqlSystemResource database that complements the master database.

3.   Diving into the three view’s definition, can be seen that all of them use the OpenRowSet function in order to make calls to INDEPROP, PARTITIONCOUNTS, ALUCOUNT internal SQL Server tables. As it seems this functionality of OpenRowSet function can’t be (mis-)used by users.

4. I found several interesting posts on sys.dm_db_partition_stats, for example Cindy Gross’ post, a Support Engineer at Microsoft, the blog containing several scripts and links to more resources.