If you don't require an exact answer, don't 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 | Refers 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:
SELECT so.[name] as [table name],RowCnt ,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]
Home