Getting a row cound without doing a table scan.

				Brought to you by Novick Software http://www.NovickSoftware.com/



Visit http://www.NovickSoftware.com for additional material about SQL Server, Visual Basic, ASP, XML, and security. 



Visit the T-SQL UDF of the Week at:
http://www.NovickSoftware.com/UdfOfWeek/UDFofWeek.htm


Check out the UDF Frequently Asked Questions at:
http://www.novicksoftware.com/UdfOfWeek/UDF_FAQ.htm


To ask a UDF related question or to contribute a UDF,
send e-mail to udf@NovickSoftware.com


Sign up for this newsletter at:

http://www.novicksoftware.com/UdfOfWeek/UDFofWeekSignup.htm




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 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]


(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 they have a heap (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.
*
* Example:
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


(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




udf_Tbl_RowCount can't be used in all situations but most of the time it gives a sufficiently accurate result for managing my databases.


 

If you like this artile, get one every week with the free T-SQL UDF of the Week Newsletter.