Determine the number of rows in a table without a table scan

• 
  Andrew Novick

  Novick Software Inc.
• Published: 11 Feb 2005

If you don't require an exact answer, it isn't necessary use a SELECT count(*) query on the rows in a table to get the row count. SQL Server keeps the row count in sysindexes and it can be retrieved there. The key is to select the correct record from sysindexes.

Sysindexes is a system table that exists in every database. SQL Server maintains at least one row in sysindexes for every user table. A few of the most important columns are:

Column   Data Type  Description
-------- ---------- ----------------------------------------
id       int        ID of the table referred to by this row
indid    int        See the text that follows...
rowcnt   bigint     Number of rows in the index 

The indid column tells us what part of the table structure this row of sysindexes is referring to:

indid value Description
----------- ---------------------------------------------------- 
0           Table data when there is no clustered index
1           Referes to the clustered index
2 - 254     Non-clustered indexes
255         Text or Image data pages

A table will only have an entry in sysindexes with an indid value of for 0 or 1, never both. That's the entry that we're interested in because its rowcnt field gives is the number of rows in the table. There's a query that shows the table, index and indid from the pubs database:

USE pubs
GO

SELECT so.[name] as [table name]
     , CASE WHEN si.indid between 1 and 254
            THEN si.[name] ELSE NULL END 
            AS [Index Name]
     , si.indid 
     FROM sysindexes si
          INNER JOIN sysobjects so
             ON si.id = so.id
     WHERE si.indid < 2 
       AND so.type = 'U' -- Only User Tables
       AND so.[name] != 'dtproperties'
     ORDER BY so.[name]

Here are the results:

table name      Index Name            indid  
--------------- --------------------- ------ 
authors         UPKCL_auidind              1 
discounts       NULL                       0 
employee        employee_ind               1 
jobs            PK__jobs__117F9D94         1 
pub_info        UPKCL_pubinfo              1 
publishers      UPKCL_pubind               1 
roysched        NULL                       0 
sales           UPKCL_sales                1 
stores          UPK_storeid                1 
titleauthor     UPKCL_taind                1 
titles          UPKCL_titleidind           1 

As you can see from the results, most of the indexes are clustered (indid=1) but a few tables such as discounts don't have a clustered index (indid=0).

I started this newsletter with "If you don't need an exact answer..." That's because there are times when rowcnt is not the exact number of records in the table. This can be corrected by updating statistics on the table with:

dbcc updateusage
go

Here's the CREATE FUNCTION script for udf_Tbl_RowCOUNT:

CREATE FUNCTION dbo.udf_Tbl_RowCOUNT (

        @sTableName sysname  -- Table to retrieve Row Count
        )

    RETURNS INT -- Row count of the table, NULL if not found.

/*
* Returns the row count for a table by examining sysindexes.
* This function must be run in the same database as the table.
*
* Common Usage:   
SELECT dbo.udf_Tbl_RowCOUNT ('
 
  ')

* Test   
 PRINT 'Test 1 Bad table ' + CASE WHEN SELECT 
       dbo.udf_Tbl_RowCOUNT ('foobar') is NULL
        THEN 'Worked' ELSE 'Error' END
        
* © Copyright 2002 Andrew Novick http://www.NovickSoftware.com
* You may use this function in any of your SQL Server databases
* including databases that you sell, so long as they contain 
* other unrelated database objects. You may not publish this 
* UDF either in print or electronically.
***************************************************************/

AS BEGIN
    
    DECLARE @nRowCount INT -- the rows
    DECLARE @nObjectID int -- Object ID

    SET @nObjectID = OBJECT_ID(@sTableName)

    -- Object might not be found
    IF @nObjectID is null RETURN NULL

    SELECT TOP 1 @nRowCount = rows 
        FROM sysindexes 
        WHERE id = @nObjectID AND indid < 2

    RETURN @nRowCount
END 
GO

GRANT  EXECUTE  ON [dbo].[udf_Tbl_RowCOUNT]  TO PUBLIC
GO

 

Let's use it:

use pubs -- assuming the UDF was created in pubs
go

SELECT [name]
     , dbo.udf_Tbl_RowCOUNT ([name]) as [Row Count]
    FROM sysobjects
    WHERE type='U' and name != 'dtproperties'
    ORDER BY [name]
GO

Here are the results:

name                                     Row Count   
---------------------------------------- ----------- 
authors                                           24 
discounts                                          3 
employee                                          43 
jobs                                              14 
pub_info                                           8 
publishers                                         8 
roysched                                          86 
sales                                             21 
stores                                             6 
titleauthor                                       25 
titles                                            18 

Reader Feedback

Tony B. writes: I have some questions about the "rowcnt" column found in sysindexes: Is this "rowcnt" value found in sysindexes always 100% accurate? How do uncommitted transactions affect this "rowcnt" value? By the way, Sybase also holds this same "rowcnt" value; however, it is no longer stored in sysindexes but can be found the systabstats table along with some other very useful numbers such as data row size, forwarded row count, deleted row count, page count and empty page count.

Franck L. writes: Marvellous function. But have your ever heard something about sp_spaceused 'tablename'?

Andy Novick, the author, responds: Regarding sp_spaceused: sp_spaceused does return the same information. The advantage of getting this information from the function is that it's a scalar and can be used in different places in SQL. For example, you could use this in a WHERE clause such as:

SELECT *
   FROM information_schema.tables
   WHERE TABLE_TYPE = 'BASE TABLE'
         and dbo.udf_Tbl_RowCOUNT (TABLE_NAME) > 1000

to take a look at the tables that have more than 1000 rows.

Patrick W. writes: This will only work if the table has a primary key or a clustered index. (I don't know which one but I don't want to look into it.) Because of that it will not work for every table. So you return a NULL if it is not found but this is incorrect because it doesn't mean there are no records in that table.

For More Information

• Feedback: E-mail the editor with your thoughts about this tip.
• More tips: Hundreds of free SQL Server tips and scripts.
• Tip contest: Have a SQL Server tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize -- submit your tip today!
• Ask the Experts: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.
• Forums: Ask your technical SQL Server questions--or help out your peers by answering them--in our active forums.
• Best Web Links: SQL Server tips, tutorials, and scripts from around the Web.